----- Original Message -----
From: "Chris Sansom" <[EMAIL PROTECTED]>
To: "MySQL List" <mysql@lists.mysql.com>
Sent: Tuesday, May 09, 2006 11:47 AM
Subject: Sum of counts
Here comes a newbie question...
I want to get a total of entries from four tables which all match a
particular id. The result for the id I'm testing (21) should be 233. In my
naivety, I thought something like this would work:
select count(a.id) + count(b.id) + count(c.id) + count(d.guide_id)
from table_a as a, table_b as b, table_c as c, table_d as d
where a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21
...but no - I get about eight and a half million! I tried fiddling about
with joins but got no better luck. In fact if I replace those +'s with
commas I get four equal values of about 2.12 million.
In the end I got the correct result like this:
select
(select count(*) from table_a where id = 21) +
(select count(*) from table_b where id = 21) +
(select count(*) from table_c where id = 21) +
(select count(*) from table_d where id = 21)
as total
Two questions:
1 Is this the best way to do it? If not, what is?
2 This is fine in MySQL 5 (on my development platform), but 3.23 (on the
live platform until the host upgrades us) doesn't support subqueries, so
as an interim measure is there any better way than doing four separate
queries and adding up the total in the PHP script?
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. Whenever
you join tables, you need to specify what the tables have in common by
writing "joining predicates"; the number of joining predicates you usually
write is the number of tables being joined minus one. Therefore, since you
are joining four tables, you need three joining predicates: one to join the
first table to the second, one to join the second table to the third, and
one to join the third table to the fourth.
A joining predicate looks like this:
table1.col4 = table2.col3
In other words, there are two column names with an equal sign in the middle.
I think you are attempting to get the same result by saying "a.id. = 21 and
b_id = 21" (etc.) but this is not having the effect that you want. 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.guide_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
This says that:
- a row in table_a is joined to a row in table_b when the 'id' value in
table_a is identical (and non-null) to the 'id' value in table_b
- a row in table_b is joined to a row in table_c when the 'id' value in
table_b is identical (and non-null) to the 'id' value in table_c
- a row in table_c is joined to a row in table_d when the 'id' value in
table_c is identical (and non-null) to the 'id' value in table_d
- the final result should only have rows where the id columns in each of the
four tables contain 21.
The conditions you wrote, namely 'a.id = 21', 'b.id = 21', 'c.id = 21' and
'd.id = 21' are called "local predicates", i.e. conditions that affect only
one table. In your case, you've said that out of all the rows in the result
set after the joins have been done, you only want rows where a.id, b.id,
c.id, and d.id are 21.
Unfortunately, you don't have any joining clauses so MySQL joins EVERY ROW
of the first table to EVERY row of the second table and EVERY ROW of the
second table gets joined to EVERY row of the third table and EVERY row of
the third table gets joined to EVERY row of the fourth table. This is called
a Cartesian product and is usually considered very bad news because it gives
you huge result sets in which most of the rows are joined to rows to which
they shouldn't be joined. I think you wrote this query on the assumption
that your local predicates would ensure that only the correct rows were
joined but, as you can see, that isn't the case.
If you try the query I gave you, you should see that it only joins rows when
the ids are equal; then, the local predicates ('a.id = 21', etc.) ensure
that you only get the joined rows you want, namely the ones that contain
21.)
This is a very common beginner mistake. Unfortunately, the MySQL manual
doesn't yet explain how to do joins very well; this is something that is
badly needed, in my opinion.
--
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
