Hi Chris, all,
Re-inserting Chris' original 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
IMO, this is simply no task for a join,
probably not a task for any single SQL statement.
The easiest way is to have four separate "SELECT count(*) FROM table_?"
with the '?' replaced by 'a' .. 'd'.
More explanations below:
Chris Sansom wrote:
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
First, the transitive equality on the 4 "id" columns (first 3
conditions) together with one restriction to 21 (say, on "a.id")
has no different effect than the 4 conditions "= 21",
in mathematical view 3 of these 7 conditions can be dropped.
(Not "any 3", but several different combinations.)
But that is not the cause of the problem - this is the join approach:
Remember that a join does a cartesian product, this is in no way helpful
to the solution of your task!
Let us construct a minimized example: Just two tables, each with three
rows, all having that magic value 21:
Table a Table b
id cnt id cnt
21 1 21 4
21 2 21 5
21 3 21 6
Doing a natural join on the "id" column will yield 9 rows:
a.id a.cnt b.id b.cnt
21 1 21 4
21 1 21 5
21 1 21 6
21 2 21 4
21 2 21 5
21 2 21 6
21 3 21 4
21 3 21 5
21 3 21 6
Summing "a.id" and "b.id" results in 18, where the correct value is 6.
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!
See above -
demanding all 4 columns to be equal to 21
is equivalent to demanding one is 21, and all are equal,
and also equivalent to some other combinations of conditions.
If you have a mathematical education, apply your knowledge of
"transitivity" to the problem.
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).
Frankly spoken: This is the way to go!
From your problem description, there seems to be no connection between
the tables that would warrant joining them.
For a very coarse analogy:
If you have separate tables for the teachers and the pupils of a school,
and want to know the number of all peoples going there daily,
you will not join these two tables,
you will rather count them individually and then add these two values.
HTH,
Jörg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
