MYSQ joining and count problem.

Riche

Hi, I have three tables like this

handhelds_table
aid hid handheld_type enable
1 1 Casio Y
1 2 Casio Y
2 3 Ipaq Y
2 4 Ipaq Y
3 5 Motorola Y
3 6 Motorola Y

company_details
aid company_name
1 Bloggs Ltd
2 Bent Ltd
3 Turner Ltd

maintenance_table
aid maintenance_type
1 Handhelds
2 Software
3 Handhelds and Software

all three tables are linked via 'aid'

Now I can run a query to get the quanties of each handheld type for each company using this query:

SELECT company_name AS Company Name , handhelds.handheld_type AS Handheld Type , count( * ) AS Number
FROM handhelds
JOIN company_details ON handhelds.aid = company_details.aid
WHERE handhelds.enable = 'Y'
GROUP BY company_name , handheld_type
ORDER BY company_name ;

But when ever I join the maintenance table into the query like this:

SELECT company_name AS Company Name , handhelds.handheld_type AS Handheld Type , count( * ) AS Number , maintenance.maintenance_type AS 'Maintenance Type'
FROM handhelds
JOIN maintenance ON handhelds.aid = maintenance.aid
JOIN company_details ON handhelds.aid = company_details.aid
WHERE handhelds.enable = 'Y'
GROUP BY company_name , handheld_type
ORDER BY company_name ;

The count goes silly so if Bloggs Ltd had 67 Casio's the query outputs 670 Casio's.

Where am I going wrong?

Thanks for any help.
Rich

planetsim

I think you need to explain what you are expecting from the query, its very hard to know what is wrong exactly without some idea of the expected outcome. With that said I have created the following tables:

company table

aid|name
1|Bloggs Ltd
2|Bent Ltd
3|Another Company

handheld

hid|aid|handheld_type
1|1|Cassio
2|1|Ipaq
3|2|Motorola
4|3|Motorola
5|3|Nokia
6|3|Cassio

maintenance

aid|name
1|Handhelds
2|Handhelds
3|Handhelds

And ran the following query

select company.name, handheld.handheld_type, maintenance.name, count(handheld.aid) from company join handheld on handheld.aid = company.aid join maintenance on maintenance.aid = company.aid group by company.name order by company.name;

To get the following output.

name|handheld_type|name|count(handheld.aid)
Another Company|Cassio|Handhelds|3
Bent Ltd|Motorola|Handhelds|1
Bloggs Ltd|Ipaq|Handhelds|2

I also recommend against the use of back ticks (`) in your query. Its not portable, and can potentially hide issues later on.

NogDog

My hunch (without really understanding what it represents) is that you might get what you want by changing [FONT=Courier New]COUNT(*)[/FONT] to [FONT=Courier New]COUNT(DISTINCT handhelds_table.hid)[/FONT], but I wouldn't bet the house and farm on it. (I believe the problem is due to joining across two many-to-one associations between the tables on the "aid" column, so for each join found on the first two tables, it then finds each join to the third table.)

dalecosp

NogDog;11052937 wrote:
(I believe the problem is due to joining across two many-to-one associations between the tables on the "aid" column, so for each join found on the first two tables, it then finds each join to the third table.)

I would concur that this is the likely cause; it might also be solved by using a tighter JOIN (e.g., perhaps a LEFT JOIN instead of a plain Cartesian JOIN).