All geek questions in one place

Limit, select duplicate UNION control columns

I have two tables:

parameters1

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

parameters2

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

I usually JOIN the result set of these two tables with UNION 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 duplication of UNION only to the cod column, so I want to avoid duplicating 1 cod in the result set (when there is an entry with a 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 delete record 1 from table parameters1 ( 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 duplication of a UNION check to just one field or some fields? How?

The solution should work in an environment with several databases (MSSQL, PostgreSQL, MySQL).

+6
source share
3 answers
 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 )
+9
source

The solution in this link should be adapted to these environments, since it is standard SQL (you can use ISNULL if COALESCE not available). I'm not sure what FULL OUTER JOIN accessibility is, but it should also be available on all three platforms.

In your case, the solution will look like this:

 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 does not seem to support FULL OUTER JOIN , so you can use a different trick in this 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
+2
source

Modify the second join query to get only parameters2, where parameters2.id is not in parameters1.

0
source

Source: https://habr.com/ru/post/906797/

More articles:


All Articles