Jerome Provensal - 10 months ago 53

SQL Question

I have the following table:

`create table #table (`

time int,

key char(1),

val int

)

with the following data:

`insert into #table (time, key, val) values (0,"a",1)`

insert into #table (time, key, val) values (0,"b",2)

insert into #table (time, key, val) values (1,"a",10)

insert into #table (time, key, val) values (2,"b",20)

and I would like to come up with a join of that will yield the following rows/cols:

`0 a 1`

0 b 2

1 a 10

1 b 0

2 a 0

2 b 20

Which is basically the cartesian product of the values of the first 2 columns, and their associated values in the 3rd column when the value exists, otherwise 0 when null.

I tried a few combinations of outer join but none of them seems to work.

Please help.

Thanks.

Answer Source

Try this:

```
SELECT DISTINCT t1.time,
t2.key,
IF(
t1.time = t2.time AND t1.key = t2.key AND t1.value = t2.value,
t1.value,
0
) AS value
FROM table_name t1
JOIN table_name t2
ORDER BY t1.time ASC,
t2.key ASC;
```