limit, choice the UNION duplicate checking columns

Question

I have two tables like the following :

parameters1

+------+-----+
| cod  | des |
+------+-----+
|  1   | aaa |
|  2   | bbb |
|  3   | ccc |

parameters2

+------+-----+
| cod  | des |
+------+-----+
|  1   | mmm |

I usually JOIN the result set of this two tables with an UNION in this way :

SELECT  cod,  des 
FROM parameters1
UNION 
SELECT  cod,  des 
FROM parameters2

and i get this result set :

+------+-----+
| cod  | des |
+------+-----+
|  1   | aaa |
|  2   | bbb |
|  3   | ccc |
|  1   | mmm |

I want to limit the UNION duplicate checking only to the cod column, so i want to avoid that the 1 cod get duplicated in the result set (when there is a record with different name and the same cod), i want to get the name from the first table (parameters1) :

+------+-----+
| cod  | des |
+------+-----+
|  1   | aaa |
|  2   | bbb |
|  3   | ccc |

UPDATE If i remove the record 1 from the parameters1 table(DELETE FROM parameters1 WHERE cod = 1) i should get this result set :

+------+-----+
| cod  | des |
+------+-----+
|  1   | mmm |   ---> FROM parameters2
|  2   | bbb |
|  3   | ccc |

Is it possible to limit the duplicate checking of a UNION to only one field or some fields ? How ?

The solution should work on a multidatabase environment (MSSQL, PostgreSQL, MySQL).

possible duplicate of [SQL Server Distinct Union for one column](http://stackoverflow.com/questions/7338884/sql-server-distinct-union-for-one-column) — ruakh, Jan 24 '12 at 22:41
Please explain the logic of why the row `(1, 'aaa')` is chosen over the row `(1, 'mmm')` e.g. because `'aaa' < 'mmm'`, perhaps? The accepted answer seems to pick an arbitrary row. — onedaywhen, Jan 26 '12 at 11:25
It is logic that i have defined because one table have precedence ... — aleroot, Jan 26 '12 at 11:51

Michael Fredrickson · Accepted Answer · 2012-01-25T03:17:59.110

10

SELECT  cod, des 
FROM parameters1 p1
UNION ALL
SELECT  cod, des 
FROM parameters2 p2
WHERE NOT EXISTS (
    SELECT 1
    FROM parameters1 p1sub
    WHERE p1sub.cod = p2.cod
)

edited Jan 25 '12 at 03:17

answered Jan 24 '12 at 22:47

Michael Fredrickson

36,839
5
92
109

@ypercube Very good point, don't need to try to filter out any duplicates here... updated. – Michael Fredrickson Jan 25 '12 at 03:18

score 2 · Answer 2 · edited May 23 '17 at 12:14

2

The solution in this link should be adaptable to those environments, as it's standard SQL (you can use ISNULL if COALESCE is not available). I'm not sure what the availability of FULL OUTER JOIN is, but it should be available on all three platforms as well.

In your case, the solution will end up looking like:

SELECT  p1.cod,  ISNULL(p1.des, p2.des) AS des
FROM parameters1 p1
    FULL OUTER JOIN parameters2 p2 ON p1.cod = p2.cod

...but... MySQL apparently doesn't support FULL OUTER JOIN, so you can use another trick in that case:

SELECT  p1.cod,  ISNULL(p1.des, p2.des) AS des
FROM parameters1 p1
    LEFT JOIN parameters2 p2 ON p1.cod = p2.cod
UNION ALL
SELECT  p2.cod, p2.des
FROM parameters2 p2
    LEFT JOIN parameters1 p1 ON p1.cod = p2.cod
WHERE p1.cod IS NULL

edited May 23 '17 at 12:14

Community

1
1

answered Jan 24 '12 at 22:39

mwigdahl

16,268
7
50
64

the solution does not cover cases where a parameter lives only in the second table, that is the result will always be the same as a simple select from the first table – newtover Jan 24 '12 at 22:49
It doesn't work because if i remove the record from parameters 1 i don't get anything ... See the updated question, and sorry for not being much accurate in the question. – aleroot Jan 24 '12 at 23:00
Shoot. Do you have both `LEFT` and `RIGHT JOIN` support in MySQL? – mwigdahl Jan 24 '12 at 23:03

score 0 · Answer 3 · answered Jan 24 '12 at 22:43

0

Change second union query to get only parameters2 where parameters2.id is not in parameters1.

answered Jan 24 '12 at 22:43

dani herrera

48,760
8
117
177

limit, choice the UNION duplicate checking columns

3 Answers3