CODEWITHSUNDEEP
mysqlsql
Ronalds Mazītis
Ronalds Mazītis

Reputation: 341

Select SQL from multiple tables with identical structure

SELECT  * 
FROM table1, 
     table2, 
     table3 
ORDER BY id ASC 
      LIMIT

how to do this right? I'm using MySQL and all the tables have identical structure.

Upvotes: 8

Views: 11762

Answers (4)

SajjadHashmi
SajjadHashmi

Reputation: 3695

1 - While assuming from statement identical structure that all three tables have a column id which us used as a primary-foreign key in these tables. And you need record for a particular id from all these three.

SELECT  * 
FROM table1 t1, 
     table2 t2, 
     table3 t3
WHERE t1.id=t2.id AND t2.id=t3.id 
-- ORDER BY id DESC
LIMIT 10;

This will give you the top 10 records from all three tables related a particular id.

2 - Assuming you just to combine results from three different queries into one statement and prioritize them by id

SELECT  * FROM table1
UNION
SELECT  * FROM table2
UNION
SELECT  * FROM table3
--ORDER BY id DESC
LIMIT 10;
  • In case you need record from the last un-comment the statement -- ORDER BY id DESC assuming you have an incrementing id column
  • If you need more record than just change 10 in LIMIT to your required number.
  • Please note: The UNION operator selects only distinct values by default. To allow duplicate values, use the UNION ALL.

Upvotes: 4

Maulik patel
Maulik patel

Reputation: 2442

UNION is used to combine the result from multiple SELECT statements into a single result set

(SELECT u.name FROM user as u where u.active=1) UNION (SELECT pu.name FROM productuser as pu WHERE pu.active=1);

Upvotes: 1

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

You can do it like this

SELECT * FROM (
                SELECT  * FROM table1
                UNION ALL
                SELECT  * FROM table2
                UNION ALL
                SELECT  * FROM table3   
) as l
ORDER BY id ASC LIMIT 50

UNION will only work when you have same number of columns in each table

Upvotes: 13

Dan Sorensen
Dan Sorensen

Reputation: 11753

SELECT  * 
FROM table1
UNION
SELECT  * 
FROM table2
UNION
SELECT  * 
FROM table3
ORDER BY id ASC

Preferably you would specify the columns you need rather than use * to reduce the memory required.

Upvotes: 0

Related Questions