hvs hvs - 1 month ago 13
MySQL Question

Mysql conditional query to list a column from one of the tables but not both

We have two tables

table:

publisher
(
publisher_id
is unique)

+--------------+------+--+
| publisher_id | name | |
+--------------+------+--+
| 100 | abc | |
| 131 | def | |
| 355 | ghi | |
+--------------+------+--+


table:
publisher_alias
(composite index of
publisher_id
and
name
is unique)

+--------------+-------+--+
| publisher_id | name | |
+--------------+-------+--+
| 131 | xyz | |
| 131 | pqr | |
| 355 | mln | |
| 355 | opr | |
+--------------+-------+--+


I am looking for a mysql command that will generate a list of names from the
publisher
table (if alias entry is not found). If the alias entry is found for a given publisher_id, then I want all the
name
from
publisher_alias
to be included in the list (
name
from
publisher
should be omitted). The output should look like this

+--+------+--+
| | name | |
+--+------+--+
| | abc | |
| | xyz | |
| | pqr | |
| | mln | |
| | opr | |
+--+------+--+

Answer

You can use a case when clause

select case  when b.name is null then a.name else b.name end
from publisher as a
left join publisher_alias as b  on a.publisher_id  = b.publisher_id