How do I Combine these SQL SELECT queries into one SELECT statement

Question

How do I combine these two select statements into one query:

SELECT SUM( incidents )  AS fires, neighborhoods AS fire_neighborhoods
FROM (
SELECT * 
FROM `fires_2009_incident_location` 
UNION ALL SELECT * 
FROM `fires_2008_incident_location`
UNION ALL SELECT * 
FROM `fires_2007_incident_location`
UNION ALL SELECT * 
FROM `fires_2006_incident_location`
) AS combo
GROUP BY fire_neighborhoods ORDER BY fires DESC



SELECT SUM( incidents )  AS adw, neighborhoods AS adw_neighborhoods
FROM (
SELECT * 
FROM `adw_2009_incident_location` 
UNION ALL SELECT * 
FROM `adw_2008_incident_location`
UNION ALL SELECT * 
FROM `adw_2007_incident_location`
UNION ALL SELECT * 
FROM `adw_2006_incident_location`
) AS combo2
GROUP BY adw_neighborhoods ORDER BY adw DESC

So, I'd like the query to return, something like:

fire_neighborhoods  fires  adw_neighborhoods  adw
xyzNeighborhood     6      abcNeighborhood    22
jklNeighborhood     3      tuvNeighborhood    40

I want to simply combine the results of the two queries above. The two queries are independent of each other. The results of one doesn't effect the results of the other query. I simply need a way to slam the two results together into one.

If anyone has any advice, please let me know.

Thank you.

-Laxmidi

Answer 1

The example you gave indicates you want to combine the queries horizontally, but then you later stated they are completely independent. These are conflicting statements because you normally combine data horizontally when records do relate to one another. Below is my idea for combining them horizontally, but I also make note of my idea for combining them vertically below that.

It depends how you want to link them up. If you are querying based on neighborhood, you can do a join between the two larger queries on fire_neighborhoods = adw_neighborhoods, such as:

SELECT fire_neighborhoods, fires, adw
FROM (

SELECT SUM( incidents )  AS fires, neighborhoods AS fire_neighborhoods
FROM (
SELECT * 
FROM `fires_2009_incident_location` 
UNION ALL SELECT * 
FROM `fires_2008_incident_location`
UNION ALL SELECT * 
FROM `fires_2007_incident_location`
UNION ALL SELECT * 
FROM `fires_2006_incident_location`
) AS combo
GROUP BY fire_neighborhoods ORDER BY fires DESC

) AS fires
    INNER JOIN (  

SELECT SUM( incidents )  AS adw, neighborhoods AS adw_neighborhoods
FROM (
SELECT * 
FROM `adw_2009_incident_location` 
UNION ALL SELECT * 
FROM `adw_2008_incident_location`
UNION ALL SELECT * 
FROM `adw_2007_incident_location`
UNION ALL SELECT * 
FROM `adw_2006_incident_location`
) AS combo2
GROUP BY adw_neighborhoods ORDER BY adw DESC

) AS adw
    ON fires.fire_neighborhoods = adw.adw_neighborhoods

This is just an example. You may need a different join or something to make it work for you.

Now, you stated that the two queries are independent and do not affect one another. If they really do have no common ground, you should add a column to each query indicating the query it came from (e.g. add a column with a constant value of 1 for the fire query and a column with a constant value of 2 for the adw query). Then, just UNION the two large queries together. This would combine them in a vertical fashion as opposed to a horizontal fashion.