Page 1 of 1

Query help - ?

Posted: Thu Jul 04, 2002 12:57 pm
by fatalcure

Code: Select all

$query = "SELECT count(a)+count(b) AS total FROM tableA, tableB WHERE tableA.id = '$id' AND tableB.id = tableA.id";
The total always comes up to be 0 - even though ONE of the tables has something to count

It seems that both tables need to have something w/ that ID for it to work, is there a way to do that count without using two queries?

Like, if there is something to count in one table, but not in the other?

Please help, Thanks

Posted: Fri Jul 05, 2002 7:30 am
by mikeq
You have answered your own question really. You are using an EQUI join between the tables, i.e. records must exist in both tables to produce a result.

If you do

SELECT a, b FROM tableA, tableB WHERE tableA.id = '$id' AND tableB.id = tableA.id";

and there are no records on one side you will get no results returned (by the way, you should get into the habit of doing this, breaking down your query to this low level and make sure you get the results you expect before aggregating stuff).

You need to look at LEFT,RIGHT,OUTER JOINS. Go to the mysql site and have a read through the manual.