Wilskt Wilskt - 7 months ago 18
SQL Question

Union of SQL tables, but only taking the first instance of an ID

I have three tables with identical columns that I need to union together into one table. Some IDs appear in more than one table, and I need to use the ID from the lowest numbered table first, and ignore that ID in any subsequent tables. Note that only the ID is the same, there aren't any duplicate rows between the tables.

So as an example, these three tables need to be transformed to the final table:

**Table 1**
+----+----+----+
| ID | C1 | C2 |
+----+----+----+
| 01 | AA | BB |
| 02 | CC | DD |
| 03 | EE | FF |
+----+----+----+

**Table 2**
+----+----+----+
| ID | C1 | C2 |
+----+----+----+
| 03 | GG | HH |
| 04 | II | JJ |
| 05 | KK | LL |
+----+----+----+

**Table 3**
+----+----+----+
| ID | C1 | C2 |
+----+----+----+
| 01 | MM | NN |
| 04 | OO | PP |
| 06 | QQ | RR |
+----+----+----+

**Resulting Table**
+----+----+----+
| ID | C1 | C2 |
+----+----+----+
| 01 | AA | BB |
| 02 | CC | DD |
| 03 | EE | FF |
| 04 | II | JJ |
| 05 | KK | LL |
| 06 | QQ | RR |
+----+----+----+


I have a vague idea of a solution using left joins and 'IS NULL' conditions, but that could get complicated very quickly, especially if the number of tables increases.

Is there a way of specifying duplicates only on one column, not on a whole row?

Answer

Since you are using Access, you can use FIRST function, and I think you only need this:

SELECT ID, First(C1) As C1, First(C2) as C2
FROM (
  SELECT * FROM Table1 UNION SELECT * FROM Table2 UNION SELECT * FROM Table3 )
GROUP BY ID
Comments