How to Make Multiple Select with a Common Where Clause in Single Query?

Question

How could I create a single query for all values from more than 1 tables where a county is equal to 'baawa'. I am using PHP to connect to the database.

What I tried:

    SELECT (                
            SELECT * FROM health WHERE county='baawa'
            ),
            (
            SELECT * FROM hotspots WHERE county='baawa'
            ),
            (
            SELECT * FROM markets WHERE county='baawa'
            ),
            (
            SELECT * FROM schools WHERE county='baawa'
            ),
            (
            SELECT * FROM security WHERE county='baawa'
            )

Edit: The tables have different number of columns.

Tables columns:

Health facility_id, name, ward, location, lat, lon, staff, staff_category, structure, emergencies, conflict_cases, communication_facility, power

Hotspots name, lat, lon, ward, location, mgt_committee, security, type

Market market_id, name, ward, location, lat, lon, mgt_committee, security

Schools Full texts, school_id, name, level, ward, location, lat, lon, students, staff_no, peace_club, conflict_affected

Security security_id, name, ward, location, lat, lon, staff, structure, transport, communication, power, crimes, resolutions

All of these tables have exactly the same column structure? If so, the best solution would be to combine them into _one table_ which ahs a column for `type` as in `type = 'security'` or `type = 'markets'` — Michael Berkowski, Nov 30 '14 at 13:42
@ Michael Berkowski, the tables have different columns. The question is not duplicate because that one deals with As, not Where clause and I have tried it in where clause and didn't work. — wondim, Nov 30 '14 at 14:01
The duplicate vote was removed. I've been asking you to provide an example of what your query output should be, to remove that confusion. If the tables have different columns, combining them in a sensible way is difficult. Please post an example of the structure of at least a couple of the tables, and what you want the query to produce. — Michael Berkowski, Nov 30 '14 at 14:03
Thank you Michael, I will update the question with the structure of the table. — wondim, Nov 30 '14 at 14:05
I have updated the question. But I still see the duplicate notice and my point is -1. :) — wondim, Nov 30 '14 at 14:18
Now we have the table structures, what output are you hoping for? If you wanted to just be able to issue a query to all the tables with the same `WHERE` clause and get all their columns back, it is not possible. You will need 5 separate queries for 5 separate table structures. If what you want is a _common subset_ of columns (like `name,ward,lat,lon` for example) then it is doable. — Michael Berkowski, Nov 30 '14 at 14:24
Oh that is not unfortunate. What I wanted to do was to create a web map. The most important ones are lat long and maybe name, ward. So when one select checkbooks that corresponds to db tables and drop downs that corresponds to wards == county, I want to get all the table info so that we use the information for using the lat and lon to point the objects in map and the rest of the information, like name, and all other columns as a popup info, that loads as Google map popup. — wondim, Nov 30 '14 at 14:35

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Since SQL queries return 2-dimensional rowsets, the output must have a consistent set of columns. It is therefore not possible to issue one query to several significantly different tables, and produce output that includes all columns for all involved tables^{see footnote}. If you want all columns to be available such as SELECT * would produce, you will need to issue 5 separate queries and fetch the rows from the individually.

What can be done with your existing structure:

With your existing structure, to get a combined rowset of all these identical, you will need to chain them together with UNION ALL using the pattern:

SELECT col1, col2 FROM health WHERE county = 'baawa'
UNION ALL
SELECT col1, col2 FROM hotspots WHERE county = 'baawa'
UNION ALL
SELECT col1, col2 FROM markets WHERE county = 'baawa'
UNION ALL....
SELECT col1, col2 FROM schools WHERE county = 'baawa'
UNION ALL
SELECT col1, col2 FROM security WHERE county = 'baawa'
/* ORDER BY applies to the whole rowset */
ORDER BY col2 DESC, col1 ASC

The catch is that you can only get columns which are common to all tables using this method in the simplistic way I have done here. Looking over your table structures, the common columns I see are name, ward, location, lat, lon. So you must list them in the same order in each UNION component's SELECT.

Notice that I do not use SELECT * on a UNION query. Unless the tables are exactly the same, having had the columns defined in exactly the same order originally, SELECT * will not produce the columns in the same order and your output won't make sense because they don't align across the UNION'd tables.

When doing a UNION query it is important to be explicit about the order of the columns in the SELECT list. In your case, this would not be possible anyway because the tables differ significantly.

If you needed to include one or two other columns in the resultant query which are not common to all tables, you can do it in the SELECT list by adding a NULL in the UNION components where the column does not exist. For example mgt_committee column for the 2 where it exists, and NULL where it doesn't:

/* NULL as mgt_committee where column doesn't exist in table... */
SELECT col1, col2, NULL AS mgt_committee FROM health WHERE county = 'baawa'
UNION ALL
SELECT col1, col2, mgt_committee FROM hotspots WHERE county = 'baawa'
UNION ALL
SELECT col1, col2, mgt_committee FROM markets WHERE county = 'baawa'
UNION ALL
SELECT col1, col2, NULL AS mgt_committee FROM schools WHERE county = 'baawa'
UNION ALL
SELECT col1, col2, NULL AS mgt_committee FROM security WHERE county = 'baawa'

Footnote:

The only way to include all columns from all tables in one SELECT would be to list the superset of all of them as I did with mgt_committee, meaning all columns from all tables must be represented in each component of the UNION, with NULL replacing the columns that don't exist. Don't do this, it doesn't make sense.

Thank you very much for the help and detailed answer. I have now understood it. Is it possible to create a new common table on the fly which will have all the columns of all tables? The use of this would be to avoid going to the code or the db when new data is added. — wondim, Nov 30 '14 at 18:55
You could create a view which covers all columns of all tables like `CREATE VIEW all_combined AS (SELECT ...UNION ALL SELECT.... UNION ALL SELECT...);` and the view is then used just like a table: `SELECT * FROM all_combined WHERE...` http://dev.mysql.com/doc/refman/5.0/en/create-view.html — Michael Berkowski, Nov 30 '14 at 21:03

How to Make Multiple Select with a Common Where Clause in Single Query?

1 Answers1

What can be done with your existing structure: