So, for example, I have 3 tables
Table 1: name
id | name | time
1 | Bob | 20.42
Table 2: item
id | item | time
1 | Bottle | 18.42
2 | Box | 21.42
Table 3: food
id | Food | time
1 | Steak | 19.42
So I want to display them like this
From table item: 1 | Bottle
From table food: 1 | Steak
From table name: 1 | Bob
From table item: 2 | Box
Use UNION ALL:
select id, name
from (
select * from name
union all
select * from item
union all
select * from food ) t
order by t.`time`
DROP TABLE IF EXISTS name;
CREATE TABLE name
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,name VARCHAR(12) NOT NULL
,time TIME NOT NULL
);
INSERT INTO name VALUES
(1,'Bob','20:42:00');
DROP TABLE IF EXISTS item;
CREATE TABLE item
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,item VARCHAR(12) NOT NULL
,time TIME NOT NULL
);
INSERT INTO item VALUES
(1,'Bottle','18:42:00'),
(2,'Box','21:42:00');
DROP TABLE IF EXISTS food;
CREATE TABLE food
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,food VARCHAR(12) NOT NULL
,time TIME NOT NULL
);
INSERT INTO food VALUES
(1,'Steak','19:42');
SELECT *, 'name' source FROM name
UNION ALL
SELECT *, 'item' FROM item
UNION ALL
SELECT *, 'food' FROM food
ORDER BY time;
+----+--------+----------+--------+
| id | name | time | source |
+----+--------+----------+--------+
| 1 | Bottle | 18:42:00 | item |
| 1 | Steak | 19:42:00 | food |
| 1 | Bob | 20:42:00 | name |
| 2 | Box | 21:42:00 | item |
+----+--------+----------+--------+