CODEWITHSUNDEEP
mysqljoin
Brds
Brds

Reputation: 1075

selecting certain fields from multiple tables without any joins

I have 5 tables (tbl1, tbl2, tbl3, tbl4, tbl5) that all have similar columns, but no fk's shared. I need to compile a list of items in order of their timestamp (a column that each table has). I've tried the following, but it didn't work:

SELECT 
    t1.id, t1.name, t1.created,
    t2.id, t2.name, t2.created,
    t3.id, t3.name, t3.created,
    t4.id, t4.name, t4.created,
    t5.id, t5.name, t5.created
FROM tbl1 t1, tbl2 t2, tbl3 t3, tbl4 t4, tbl5 t5;

Obviously, the above wouldn't sort by the timestamp column, but I need to get something like this working before I add in the "ORDER BY" clause.

Is there a way to (without creating a view) run a query which returns the data from 5 tables (event if some tables are empty)?

Upvotes: 0

Views: 202

Answers (2)

spencer7593
spencer7593

Reputation: 108400

Sounds like what you are looking for is the UNION ALL operator.

SELECT t1.id, t1.name, t1.created FROM tbl1 t1
 UNION ALL 
SELECT t2.id, t2.name, t2.created FROM tbl2 t2
 UNION ALL 
SELECT t3.id, t3.name, t3.created FROM tbl3 t3
 UNION ALL 
SELECT t4.id, t4.name, t4.created FROM tbl4 t4
 UNION ALL 
SELECT t5.id, t5.name, t5.created FROM tbl5 t5
 ORDER BY 3

The UNION ALL operator basically concatenates the results of the queries into a single result set. Note that the number of expressions and the datatype of each expression must match in all of the queries. (If you need to include an extra column in one of the queries, you need to add corresponding expression (even a dummy placeholder) in the other queries.

When I do queries like this, it's sometimes helpful to include an expression (column) which tells me which source query returned the row:

SELECT 't1' as source, t1.id, t1.name, t1.created FROM tbl1 t1
 UNION ALL 
SELECT 't2', t2.id, t2.name, t2.created FROM tbl2 t2
 UNION ALL 
SELECT 't3', t3.id, t3.name, t3.created FROM tbl3 t3
 UNION ALL 
SELECT 't4', t4.id, t4.name, t4.created FROM tbl4 t4
 UNION ALL 
SELECT 't5', t5.id, t5.name, t5.created FROM tbl5 t5
 ORDER BY 4

Note that the ORDER BY applies to the entire result set, and you have to reference the expressions to sort on by column position.

Upvotes: 1

Fahim Parkar
Fahim Parkar

Reputation: 31637

Use UNION ALL

SELECT id, name, created FROM tbl1
UNION ALL
SELECT id, name, created FROM tbl2
UNION ALL
SELECT id, name, created FROM tbl3
UNION ALL
SELECT id, name, created FROM tbl4
UNION ALL
SELECT id, name, created FROM tbl5

Upvotes: 0

Related Questions