Query help - ?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
fatalcure
Forum Contributor
Posts: 141
Joined: Thu Jul 04, 2002 12:57 pm
Contact:

Query help - ?

Post 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
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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.
Post Reply