Counting total rows of multiple tables matching criteria.

Hi,
I'm using PHP and MySQL.
Look at these example tables:

id num name colour
1 4 dave red
2 3 jane green
3 4 bob NULL


id num name colour
1 4 claire blue
2 3 john NULL
3 2 mike NULL



I want to count the total number of rows in each table where 'num' is '4' and 'colour' in not null. So the answer in the example would be 2. (dave and claire would be counted.)

Thanks!

 

Never mind, i've found a solution.
Thanks for looking!


For the benefit of anyone who finds this page with a similar problem:

$sql_rowcount="SELECT COUNT(DISTINCT table1.id) + COUNT(DISTINCT table2.id) as rowtotal from table1, table2, where table1.id='46' and table1.colour is not null and table2.auditid='46' and table.colour is not null ;";

 

Looks like I spoke too soon!
The above code doesn't work if one of the tables matches no rows. It returns 0. Any ideas?

 

Try this (I didn't check if it works):

SELECT SUM(a.numberfound) 
from 
  (SELECT COUNT(DISTINCT table1.id) AS numberfound
     FROM table1
     WHERE table1.num='4' 
     AND table1.colour is not null
   UNION
   SELECT COUNT(DISTINCT table2.id) AS numberfound
     FROM table2
     WHERE table2.num='4' 
     AND table2.colour is not null)
         AS a

PHP:

One question: why do you have two tables that are identical? Couldn't you merge them into one table, with another field to identify whatever it is that separates them? It sure would make this query a lot easier.