CODEWITHSUNDEEP
phphtmlmysqlsql
Pedro Estevão
Pedro Estevão

Reputation: 1074

Query two tables ordered by "date" field

I have two tables in my mySQL database. The two tables are completely different, have nothing in common, but both of them have a "date" field.

I want to, via PHP, get all rows from both tables ordered by the "date" field.

Example:

-----------------------------------------------
name     |   category     |   date
-----------------------------------------------
PSO      |      Food      |   2015-09-16
TSI      |      Sport     |   2015-10-12
-----------------------------------------------

-----------------------------------------------
film           |   date
-----------------------------------------------
Harry Potter   |   2015-09-19
Star Wars      |   2015-11-14
-----------------------------------------------

The result I expect is that:

PSO, Food, 2015-09-16
Harry Potter, 2015-09-19
TSI, Sport, 2015-10-12
Star Wars, 2015-11-14

How can I do that?

PS: each row of tables will be a div on my webpage and these div's must be ordered by date.

Upvotes: 0

Views: 47

Answers (3)

jqheart
jqheart

Reputation: 767

Try

SELECT name, category, concat('') AS film, date 
FROM TABLE_1 ORDER BY `date` 
UNION 
SELECT concat('') AS name, concat('') AS category, film, date 
FROM TABLE_2 
ORDER BY `date` ;

Since UNION expects to have the column names similar while doing union operation you might need to use custom columns with empty values.

Upvotes: 0

Sumesh TG
Sumesh TG

Reputation: 2575

select name ,      
category,
date from table1
union all
select film as name , null as category , date
from table2  
order by date;

Make sure the aliases of both table are same and ordered currectly.....

Upvotes: 0

Rahul
Rahul

Reputation: 77866

Looks like you are looking for UNION but the result won't be same as what you posted; since UNION poses restriction that both table must have same number of columns but you can insert a NULL for missing column though like below.

select * from (
select name,      
category,
`date` from table1

union

select film, null, `date`
from table2) xx
order by `date`;

Upvotes: 1

Related Questions