[SOLVED] Odd results from a count

[Buchead]

Hello,

I'm getting odd results from a count and wonder if someone could point out what I'm doing wrong....

Have 3 tables:

Products - containing 'id' and 'product'

Orders - containing 'orderid' and 'productid'

Cancelled - also containing 'orderid' and 'productid'

In both tables, orderid is a unique number, and productid relates to the 'products' table.

I'm trying to get a count of products from both tables by using:

[code]SELECT p.name,COUNT(o.productid)+COUNT(c.productid) AS total FROM `products` AS p LEFT JOIN `orders` AS o ON o.productid=p.id LEFT JOIN `cancelled` AS c ON c.productid=p.id GROUP BY p.id ORDER BY total DESC,p.name ASC[/code]


If a product has 4 references in orders and 2 in cancelled it's coming back with a total of 16!  By only having one other table in the query it returns the correct value.


What am I doing wrong?

Thanks,

Clive.

[fenway]

You'll probably find that you're getting back too many rows...

[Buchead]

Thanks, but how can I achieve the output required?  Other than performing 2 separate counts on the tables and combining the results outside the query. Or is that the best solution?

Thanks.

[fenway]

Can you post some sample data, and the table structures?

[Buchead]

Not the live data, but something like this:

`products` table:

id      name
1       Socket set
2       Sledge hammer
3       hard hat
4       3" nails
5       flat-head screwdriver

basically consists of items that can be ordered. 'id' being a unique, incremental number.

`orders` table:

orderid     productid
1                 2
2                 3
2                 4
3                 1   
4                 2
4                 3
4                 5
5                 4

Got it slightly wrong before. The 'orderid' column relates to another table, but the 'productid' relates directly to an item in the `products` table.

`cancelled` table is the same structure as `orders`. an 'orderid' relates to an order that had been cancelled, while 'productid' relates to the `products` table.


What I want to achieve is an individual count of all the products from both tables. I can get a successful count by only doing a count on `orders` or `cancelled`, but what to know the total count for a product from `products` from both tables. The more enteries there are in both tables, the higher the count is.

I've got around it by performing 2 individual counts and combining the results, however, hoped to achieve it directly from one query.

Hopefully this makes sense.

[fenway]

Try this:

[code]
SELECT p.name, COUNT(co.productid) AS total FROM `products` AS p LEFT JOIN ( SELECT * FROM `orders` UNION ALL SELECT * FROM cancelled ) AS co ON co.productid=p.id GROUP BY p.id ORDER BY total DESC, p.name ASC
[/code]