来自第三表的内部连接的两个表的SQL计数

Question

My database has two similar tables with different names. One of the columns contained in both tables is called "zips", which contains a zip code.

I have a different table (US zip code table) where each entry contains a county name, county ID, state, state ID, and zip code.

I need a query that will tell me how many entries in the first two tables match a given county ID, and will group the results by county.

That is, lets say table A has 4 entries for zip code '30017', and table B has 1 entry for '30017'. In the US zip code table, '30017' corresponds to the county name "Gwinnett" and the county ID '839'. When I run the query the desired result is:

Gwinnett    5

Here is the latest query I've tried. I have no idea what numbers it's returning. It's definitely not what I'm expecting, as I've tried the individual component queries to test the results.

SELECT b.County, COUNT(*) as Calls FROM (aLeads a, pLeads p)
INNER JOIN zipCodes b ON a.zip = b.ZipCode
WHERE b.countyID IN (2897, 2146, 839) 
AND a.callDate BETWEEN '2013-10-01' AND '2013-11-30'
GROUP BY b.County

Any ideas?

Answer 1

Try starting with the zipcode table, and joining it with aLeads and pLeads:

SELECT b.County, COUNT(*) as Calls 
FROM zipCodes b 
INNER JOIN aLeads a ON a.zip = b.ZipCode
INNER JOIN pLeads p ON p.zip = b.ZipCode
WHERE b.countyID IN (2897, 2146, 839) 
GROUP BY b.County