At 13:28 -0400 9/5/06, Rhino wrote:
The reason you are getting so many rows has nothing to do with the
way you are using the count(*) function and adding the different
count() results together. The problem is that you are doing your
joins incorrectly... In your case, I think you need to change the
original query to this:
select count(a.id) + count(b.id) + count(c.id) + count(d.id)
from table_a as a, table_b as b, table_c as c, table_d as d
where a.id = b.id
and b.id = c.id
and c.id = d.id
and a.id = 21
and b.id = 21
and c.id = 21
and d.id = 21
Hi Rhino
Many thanks for the very full and frank response, but sadly it didn't
work. I do understand exactly what you said, and I even took it
further, adding in:
and a.id = c.id
and a.id = d.id
and b.id = d.id
...so that every table is thus related to every other one, but I'm
/still/ getting that damned eight and a half million instead of the
233 I expect!
I'm baffled by this, though the version I did with subqueries works
very nicely (and it's simple enough to do four separate queries and
add them together in the script for the older MySQL).
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
Outside of a dog a man's best friend is a book.
Inside of a dog it's too dark to read.
-- Groucho Marx
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
