NewKid NewKid - 4 months ago 7
SQL Question

SQL join - result in single row

I am trying to join two SQL tables to get a result in a single line

Table 1

id | num | value (Values can be 'N' number)
------ | ------ | ------
X | 1 | ZA
X | 2 | Z1


Table 2

id | num | num2
------ | ------ | ------
X | 1 | 2


Joing table 1 and table 2

What I got:

id | num | value | num | value
------ | ------ | ------ | ------ | ------
X | 1 | ZA | 2 | null
X | 1 | null | 2 | Z1


Expected result:

id | num | value | num | value
------ | ------ | ------ | ------ | ------
X | 1 | ZA | 2 | Z1


How can I do that?

Answer
Select t1.id, t1a.num, t1a.value, t1b.num, t1b.value
from table2 t2
left join table1 t1a on t2.num = t1a.num
left join table1 t1b on t2.num2 = t1b.num
Comments