Re: SQL and IN

Christoph Seidel wrote:

> When i query for: select * from Table where Id IN (1,2,4) i will get 2
> rows:
>
> 1, foo1, bar1
> 2, foo2, bar2
>
> But i need 3 rows, Id 1 and 2, with the data of these rows and 4 with all
> column values set to null besides Id which shall be 4.
>
> 1, foo1, bar1
> 2, foo2, bar2
> 4, null, null

If I understand you correctly, it is like showing the number of widgets (e.g. invoices, calls, claims, whatever) per day.

However, for certain days, there may be no widgets. You want however to see that, e.g.

DAY WIDGETS
 1 200
 2 0 (no rows in WIDGET table)
 3 10

To do this type of thing, I usually create a second table and populate it with the distinct list of "must-exist" values. It works pretty well in the Warehouse environment in the form of a "date dimension" table.

You do an outer join between the date table and the widget table - which means that for every single day in the date table you will have a row in the SQL projection, even though that date may not exist in the widget table.

--
Billy