mysql select query help -- ORDER BY

Question

I'm trying to construct a select query where it will take all the columns of 4 tables and then order and display the results by the column 'name' (the same in all tables). I'm still learning the ropes of MySQL.

I'm finding that because the columns share the name 'name', only the results from the last table are displayed. Is there a way of performing this query which retains all the data from all the tables?

Should I use different column names? It seems easier to share one name since it is the same information in each.

SELECT * FROM table_one, table_two, table_three, table_four ORDER BY...

The four tables not joined, and the structure is different... Some column names are shared (which it looks like I should fix, I still can at this point), but each has a different amount of columns.

Thank you!

Answer 1

Yes you should use different column names, but to get all data also you can write quesries like this:

SELECT table_one.* t1, table_two.* t2, table_three.* t3, table_four.* t4 FROM table_one, table_two, table_three, table_four ORDER BY...

Answer 2

If there's no relationship between the four tables, use UNIONs instead:

SELECT a.name
  FROM TABLE_ONE a
UNION
SELECT b.name
  FROM TABLE_TWO b
UNION
SELECT c.name
  FROM TABLE_THREE c
UNION
SELECT d.name
  FROM TABLE_FOUR d
ORDER BY name

There's two options here - UNION is slower, because it will remove duplicates - the final list will be a unique list of names. UNION ALL is faster because it doesn't remove duplicates.

To get the columns from the tables as well, use:

SELECT a.*,
       b.*,
       c.*,
       d.*
  FROM (SELECT a.name
          FROM TABLE_ONE a
        UNION
        SELECT b.name
          FROM TABLE_TWO b
        UNION
        SELECT c.name
          FROM TABLE_THREE c
        UNION
        SELECT d.name
          FROM TABLE_FOUR d) x
LEFT JOIN TABLE_ONE a ON a.name = x.name
LEFT JOIN TABLE_TWO b ON b.name = x.name
LEFT JOIN TABLE_THREE c ON c.name = x.name
LEFT JOIN TABLE_FOUR d ON d.name = x.name

Answer 3

johnny_n,

You should use

 SELECT name as name1, name as name2, name as name3 etc...

Obviously you need to use the correct syntax, but using the AS keyword, will allow you to use the key you want in your query.

Answer 4

If they all share the same name's....

SELECT * 
    FROM table_one 
LEFT JOIN
    table_two USING(name)
LEFT JOIN
    table_three USING(name)
LEFT JOIN
    table_four USING(name)
ORDER BY name