Mysql Question posted in
The official MySQL documentation can be found here.

Using multiple tables for lookup in MySQL

I’m normally pretty good at finding the answer I need by searching, but I’m drawing a blank on this one.

Let’s say I have two tables that both have a list of names and ID numbers (we’ll call them a and b). There is some other data in each of these tables which is why they are different tables, but for this query all I need is the name. What I currently have is something along the lines of:

SELECT c.number, c.field, a.name FROM c LEFT JOIN a ON (c.number = a.number)

However, it’s possible that the number I’m looking for is not in a but is instead in b. How can I retrieve a.name is my number is in table a but b.name if the number is in table b? The code that loads this data (and other policies about this number) ensures that it will only exist in one table or the other.

Tags:

2

Answers


  1. 0

    You can use COALESCE

    SELECT c.number, c.field, COALESCE(a.name, c.name) AS name
FROM c 
FULL JOIN a ON c.number = a.number
    Login or Signup to reply.
  2. 0

    Just join both:

    select c.number, c.field, coalesce(a.name, b.name) name
from c
left join a on a.number = c.number
left join b on b.number = c.number

    Some are suggesting you join a union instead:

    select c.number, c.field, ab.name
from c
left join (
    select a.number, a.name from a
    union all
    select b.number, b.name from b
) ab on ab.number = c.number

    but that is unlikely to perform well.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search