Mysql+join+count

[gptsven]

I have this query, when I echo package_votes I get the number 5, while there actually only is one record!

<?php  
$sQuery =  
" 
SELECT  
    hosts.id AS iD, 
    DATE_FORMAT(hosts.date_added,'%Y-%m-%d' ) AS added, 
    DATE_FORMAT(hosts.date_updated,'%Y-%m-%d' ) AS updated, 
    hosts.name, 
     
    package_main.freehost_id, 
    package_main.diskstorage, 
    package_main.bandwidth, 
    package_main.forcedads, 
     
    package_ratings.freehost_id, 
    uptime_ratings.freehost_id, 
    overall_ratings.freehost_id, 
    COUNT(uptime_ratings.freehost_id) AS package_votes 
     
     
     
     
     
     
     
     
     
     
     


FROM hosts  
     
LEFT JOIN package_main ON hosts.id = package_main.freehost_id  
LEFT JOIN package_ratings ON hosts.id = package_ratings.freehost_id 
LEFT JOIN uptime_ratings ON     hosts.id = uptime_ratings.freehost_id 
LEFT JOIN overall_ratings ON hosts.id = overall_ratings.freehost_id 



GROUP BY     
    hosts.id, 
    hosts.date_added, 
    hosts.date_updated, 
    hosts.name, 
    package_main.freehost_id, 
    package_main.diskstorage, 
    package_main.bandwidth, 
    package_main.forcedads, 
    package_ratings.freehost_id, 
    uptime_ratings.freehost_id, 
    overall_ratings.freehost_id 
     
     
ORDER BY  

    date_added DESC LIMIT ".$aNavigatie['iVanaf'].", ".$iMaxPerPagina 


; 
?>

I totally do not get what is wrong :

[kapisco]

You see the problem lies in using the left joins. any row even it doesn satisfy the join condition will be fetched that's why u r getting a wrong count.
so either use inner joins or post ur table structures and the result you want to get maybe i can help...

[garrettroyce]

Try using DISTINCT. I have a feeling you're getting the same data more than once.

[gptsven]

well distinct didnt work...

left join is positively the right choice here. I'm certain of one thing and that's it :P

[garrettroyce]

Count is a really tricky function, as innocuous as it may seem. I'll try to review this more thoroughly later

[gptsven]

the number 5 comes from the table package_ratings, where 5 records exist. but i thought i told sql to fetch me the number of records in uptime_ratings :/ I'm like wtf?

[kapisco]

gptsven
I gave you the true solution for the problem it's the left join if you fail to see that it's up to u i've done my work here...

[misson]

What are the structures of the tables? I suspect you need a more restrictive join.

Edit: I believe the DISTINCT option for SELECT operates on the final result set, after COUNT() has merged grouped rows, which is why it didn't help.

Also, freehost_id is probably only part of the join domain you need for the ratings tables, which is why there are more rows that you expected.

[garrettroyce]

Maybe this will help:

(From mysql reference)

COUNT(DISTINCT expr,[expr...])

Returns a count of the number of different non-NULL values.

COUNT(DISTINCT) returns 0 if there were no matching rows.

mysql> SELECT COUNT(DISTINCT results) FROM student;

In MySQL, you can obtain the number of distinct expression combinations that do not contain NULL by giving a list of expressions. In standard SQL, you would have to do a concatenation of all expressions inside COUNT(DISTINCT ...).

[misson]

To add to what garrettroyce said: as you're joining on uptime_ratings.freehost_id, the expression COUNT(DISTINCT uptime_ratings.freehost_id) will always be 1. There should be another field/other fields you can use in a COUNT(DISTINCT ...) that should work, the same field(s) you can use to restrict the join.