Mysql Subquery - Need to make a connection between two like queries in order to Count Occurrences of Postal Codes and Zip Codes

Question

I'm having a bit of an issue figuring this out. I have two queries that I need to put together and no matter what I've tried I always end up with an error in mysql syntax. The two queries are below.

Query 1 - this is for canadian postal codes

SELECT ta.city, ta.email_address, ta.country_code, COUNT(*)
FROM jos_form_submitteddata_form1 tb
JOIN jos_postalzip_redirect ta ON UPPER(LEFT(tb.FIELD_24, 3)) = LEFT(ta.postal_zip, 3)
WHERE LENGTH(tb.FIELD_24) > 5
GROUP BY ta.city;

Query 2 - This if for US zip codes

SELECT ta.city, ta.email_address, ta.country_code, COUNT(*)
FROM jos_form_submitteddata_form1 tb
JOIN jos_postalzip_redirect ta ON UPPER(tb.FIELD_24) = LEFT(ta.postal_zip, 5)
WHERE LENGTH(tb.FIELD_24) <= 5
GROUP BY ta.city
HAVING ta.country_code = 'US';

The goal is to have both of these queries display in the same table with correct counts

score 0 · Answer 1 · answered Jul 08 '11 at 17:49

A UNION doesn't do what you need?

SELECT ta.city, ta.email_address, ta.country_code, COUNT(*) 
FROM jos_form_submitteddata_form1 tb 
JOIN jos_postalzip_redirect ta ON UPPER(LEFT(tb.FIELD_24, 3)) = LEFT(ta.postal_zip, 3)
WHERE LENGTH(tb.FIELD_24) > 5 GROUP BY ta.city
UNION
SELECT ta.city, ta.email_address, ta.country_code, COUNT(*) 
FROM jos_form_submitteddata_form1 tb 
JOIN jos_postalzip_redirect ta ON UPPER(tb.FIELD_24) = LEFT(ta.postal_zip, 5) 
WHERE LENGTH(tb.FIELD_24) <= 5 AND ta.country_code = 'US' GROUP BY ta.city

score 0 · Accepted Answer · answered Jul 08 '11 at 17:50

0

Try using a UNION statement.

http://dev.mysql.com/doc/refman/5.0/en/union.html

answered Jul 08 '11 at 17:50

Paul Sonier

38,903
3
77
117

@FredB: I think UNION is the best way to do this, yeah. – Paul Sonier Jul 08 '11 at 18:22

Mysql Subquery - Need to make a connection between two like queries in order to Count Occurrences of Postal Codes and Zip Codes

2 Answers2