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
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 tablessee 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: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 eachUNION
component'sSELECT
.Notice that I do not use
SELECT *
on aUNION
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 theUNION
'd tables.When doing a
UNION
query it is important to be explicit about the order of the columns in theSELECT
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 aNULL
in theUNION
components where the column does not exist. For examplemgt_committee
column for the 2 where it exists, andNULL
where it doesn't: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 withmgt_committee
, meaning all columns from all tables must be represented in each component of theUNION
, withNULL
replacing the columns that don't exist. Don't do this, it doesn't make sense.