Join all records

[Monardo]

Dear experts

Say I have two following tables:

Table1:
ID | Item1
1 | Table
2 | Chair
3 | Shelf

Table2:
ID | Item2
2 | Book
3 | Paper
4 | Pen

I need to create query which looks like this:

ID | Item1 | Item2
1 | Table | Null
2 | Chair | Book
3 | Shelf | Paper
4 | Null | Pen

I am managing it by creating first query UNION on ID, second query group by ID (to get each number only once) and third query with query2, table1 and table2 (including all records from query2).

Nevertheless I suspect that there is easier (less queries involved) way to achieve the same.

Thank you in advance for ideas.

PS. ID in both tables are unique.

 

[apr pillai]

You are doing it right, but don't need the thrid query. You can get the result in the second query itself.

SQL of both queries are given below:

Query1

SELECT Table1.id FROM Table1
UNION ALL SELECT  Table2.id
FROM  Table2;

Query2

SELECT Query1.id, Table1.Item1, Table2.Item2
FROM (Query1 LEFT JOIN Table1 ON Query1.id = Table1.id) LEFT JOIN Table2 ON Query1.id = Table2.id
GROUP BY Query1.id, Table1.Item1, Table2.Item2;

 

[Monardo]

Thanks a lot. One query less is really nice.

 

[Monardo]

@ apr pillai

Actually I was trying your sql code and realized that if instead of UNION ALL i use just UNION in the first query, it will automatically eliminate duplicates and no need for GROUP BY anymore.

Thanks for good idea