MYSQL select from 3 or more tables

I need to make a MySQL query to show data from 3 diferents tables.

This is the table 1:

TABLE1

• id

• reference

• name

• email

This is the table 2:

TABLE2:

• id

• phone

This is the table 3:

TABLE3:

• id

• phone

I need to show all data from table1, and also the phone from table2 or table3, only if the id in table2 or table3 is the same number that is in the refe开发者_开发技巧rence field in table1.

Any advice? Thank you!

You can try something like

SELECT  t1.*
        COALESCE(t2.phone,t3.phone) phone
FROM    Table1 t1 LEFT JOIN
        Table2 t2 ON t1.reference = t2.id LEFT JOIN
        Table3 t3 ON t1.reference = t3.id

Have a look at COALESCE(value,...) and maybe SQL SERVER – Introduction to JOINs – Basic of JOINs

Yes, I have an advice, modify your structure. There's no point in having different tables to hold different phone numbers. Here's something you can do:

table1( -- you should give it a better name
  id,
  -- reference, -- not needed now...
  name,
  email
);

phone_numbers(
  id,
  table1_id,
  phone
);

Now you can do something like:

SELECT    table1.*, GROUP_CONCAT(phone) 
FROM      table1 
LEFT JOIN phone_numbers ON table1.id = table1_id
GROUP BY  table1.id, name, email -- , whatever fields you have more on table1

You asked for a phone from table2 or from table3.

Because these 2 tables have common columns, we can simplify this whole thing and think about these 2 tables as a single one, by using an UNION clause:

select table1.*, v.phone
  from table1
  inner join (select * from table2 
              union
             select * from table3) v on v.id = table1.reference

EDIT: corrected table names in the union

    SELECT t1.*, t2.*, t3.*
FROM table1 t1 JOIN table2 t2
   ON t1.reference = t2.ID
JOIN table3 t3
   ON t1.reference = t3.ID

I don't know if you can do CASE statement in select in mysql, but you can try a CASE statement as a column and join. Here is some sudo code.

SELECT  t1.*, CASE t2.phone IS NOT t3.phone THEN t3.phone ELSE t2.phone END CASE as PhoneNumber 
FROM Table1 t1 
LEFT JOIN Table2 t2 ON t1.reference = t2.id 
LEFT JOIN Table3 t3 ON t1.reference = t3.id