THIS IS ONE FOR THE GURU'S - SQL PROBLEM

[IanNav]

SELECT company.mediaredirect. * , COUNT( company_orders.old_orderdetails.id ) , COUNT( company_orders.orderdetails.id ) FROM company.mediaredirect LEFT JOIN company_orders.old_orderdetails ON company_orders.old_orderdetails.mediasource = company.mediaredirect.code LEFT JOIN company_orders.orderdetails ON company_orders.orderdetails.mediasource
= company.mediaredirect.code
GROUP BY company.mediaredirect.id
ORDER BY company.mediaredirect.count DESC


if the count of the company_orders.old_orderdetails.id is greater than 1, the next count is repeated by however many results are in the first column.

in this instance, the count of company_orders.old_orderdetails.id is 2 and the count of company_orders.orderdetails.id is 2 - when in reality, the count of the latter should = 1.

any assistance you can provide in this matter would be greatly appreciated.

 

[IanNav]

I have tried doing the 2 counts * left joins separatly and the couts are completly correct.

i think i may need to add a sub query somewhere in here to separate out the 2 counts and left joins, can anyone give me any help on how to do this. i don't know much about sub queries.

Thanks

Ian

 

[twifosp]

Try the same query without the counts and group by.

You'll see what the rows are actually returning and more often than not why.

You might have a duplicate that is being created by the join that is being counted.

Also try count(distinct column_name).