I have two tables (Table1 and Table2) that need to be joined.
The column id links Table1 and Table2.
The output table needs to contain all rows in Table1.
Table2 has duplicates which should not show up in the output table.
If a duplicate row in Table2 has NULL in a column for a given id, and if another row in Table2 has a value in this column for the same id, then the output table should contain the value instead of NULL. (i.e. like in id 002 , 003 and 005 in the example below)
If duplicate rows contain different values for the same column for a given id. Any of the values can be taken to the output table. (i.e. like in id 001 in the example below)
Example:
Table 1:
ID Value1
---- ------
001 Mary
002 Jane
003 Peter
004 Smith
005 Katy
Table 2:
ID Value2 Value3
---- ------ ------
001 25 33
001 25 38
001 NULL 33
002 NULL NULL
002 18 56
003 22 NULL
005 NULL 34
I need to join the tables and get the following result:
ID Value1 Value2 Value3
---- ------ ------ ------
001 Mary 25 33
002 Jane 47 88
003 Peter 22 NULL
004 Smith NULL NULL
005 Katy NULL 34
Thank you for your time!
See SQL Fiddle:
SELECT DISTINCT T1.ID, T1.Value1,
(
SELECT ST2.Value2
FROM Table2 ST2
WHERE T1.ID = ST2.ID
ORDER BY ST2.Value2
LIMIT 1
) AS Value2,
(
SELECT ST3.Value3
FROM Table2 ST3
WHERE T1.ID = ST3.ID
ORDER BY ST3.Value3
LIMIT 1
) AS Value3
FROM Table1 AS T1