What is the best way to combine these two queries

I keep getting mysql errors when I try to write joins or unions combining these two queries. field1A and field1B are the conditions I am trying to count.

SELECT count(*) 
FROM `table1` c1 
    LEFT JOIN `table2` j1 ON (j1.`field1A` = '1') 
WHERE (c1.`field2` = 'Yes' AND c1.`field` is null)")

and

SELECT count(*) 
FROM `table1` c1 
    LEFT JOIN `table2` j1 ON (j1.`field1B` = '1') 
WHERE (c1.`field2` = 'Yes' AND c1.`field` is null)")

I am sure there is a more elegant way than running both queries and adding the totals together.

Answer

You can get it from single select as follows.

SELECT count(*) 
   FROM `table1` c1 
        LEFT JOIN `table2` j1 ON (j1.`field1A` = '1')  AND (j1.`field1B` = '1')
    WHERE (c1.`field2` = 'Yes' AND c1.`field` is null)

Advertisement

Answer