SQL: Join tables excluding duplicates in second table

Question

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!

Answer 1

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