CODEWITHSUNDEEP
mysql
Alfonso Bueno
Alfonso Bueno

Reputation: 1

How to combine multiple Select queries in a single one MySql

In a webpage I want to display several tables with information.

To do that, at the moment I do

SELECT * FROM tableONE WHERE field LIKE 'criteria'

then I process the result in a foreach loop. Then I do another query to the next table and process it again

SELECT * FROM tableTWO WHERE field LIKE 'criteria'

.... 
SELECT * FROM tableTWENTY WHERE field LIKE 'criteria'

I've the feeling that making 20 connections with the database is suboptimal and I could make a single query and in the foreach loop put each result in the appropriate table. The issues I found to do it are:

What is the most efficient way to do this? Thanks

I've tried JOIN, UNION and separating the tables with comas (cartesian join), but they don't give the expected result

Upvotes: 0

Views: 109

Answers (1)

Dr. Botwing
Dr. Botwing

Reputation: 381

Just use UNION Clause

SELECT field1, field2, filed3 FROM table1

UNION

SELECT field1, NULL as filed2, field4 as filed3 FROM table2
;

Beware that

  • Every SELECT statement within UNION must have the same number of columns
  • The columns must also have similar data types

Upvotes: 1

Related Questions