CODEWITHSUNDEEP
mysql
Neil Reardon
Neil Reardon

Reputation: 65

mysql select from 3 different tables

I am trying to select from three different tables and order by the date columns:

table_1:
date_1, col_1a, col_1b, col_1c, col_1d, col_1e

table_2:
date_2, col_2a, col_2b, col_2c

table_3:
date_3, col_3a, col_3b, col_3c, col_3d

I am trying to achieve something like:

select * from table_1, table_2, table_3 order by (date) desc

I want the output to be ordered by the most recent date. Is this possible?

Many thanks,

Neil.

Upvotes: 0

Views: 56

Answers (2)

forpas
forpas

Reputation: 164054

Your query does a cross join of the 3 tables which produces the cartesian product of the 3 tables which is not what you want (I think).
I suspect you want UNION or UNION ALL:

select 'table_1' fromtable, date_1 date, col_1a cola, col_1b colb, col_1c colc, col_1d cold, col_1e cole from table_1
union all
select 'table_2', date_2, col_2a, col_2b, col_2c, null, null from table_2
union all
select 'table_3', date_3, col_3a, col_3b, col_3c, col_3d, null from table_3
order by date

For this code to work the corresponding columns in all 3 tables must have the same data type.

Upvotes: 2

GokuMizuno
GokuMizuno

Reputation: 543

If the tables share a common column, that is, table_1.date = table_2.date, you can do a join

SELECT * FROM table_1
    LEFT JOIN (t2 CROSS JOIN t3)
                 ON (table_2.date = table_1.date AND table_3.date = table_1.date)

If the three tables are completely distinct from each other, then no, you cannot display them in one SELECT.

Upvotes: 0

Related Questions