Dubbla resultat

Nu har det inträffat igen, och jag förstår inte varför. Jag vill hämta ut hur många kommentarer en krönika har fått samt vilket betyg den har. Men där blir det ibland dubbla resultat? Så här ser min SQL sats ut:

SELECT u.nickname,r.title,[b]Count(c.object_ID),Count(v.vote),Sum(v.vote)[/b]
FROM tblRecord r
INNER JOIN tblUser u ON u.user_ID = r.user_ID
[b]LEFT JOIN tblComment c ON c.object_ID = r.object_ID AND c.type_ID = 2
LEFT JOIN tblVotes v ON v.object_ID = r.object_ID AND v.type_ID = 2[/b]
GROUP BY r.object_ID ORDER BY r.msg_date DESC

Resultatet kan säga att det finns 2 svar när det bara finns 1 osv. Någon som vet varför?

I och med att du använder en join så får du alla kombinationer av poster, dvs om det finns 2 röster och 3 betyg på en arikel så skulle du få 6 poster (utan group by) och det är utifrån det resultatet som count/sum etc beräknas. Det är egentligen två helt orelaterade saker (antal comments och antal votes) som du joinar ihop.

Att ha två olika count för varsin kolumn ger inte det resultat du förmodar. Skillnaden mellan att ange ett kolumnnamn och att använda count(*) är att i det första fallet så räknas inte de poster som har null i kolumnen med i resultatet.

Din group by är också mycket suspekt. Normalt så skall alla kolumner i select-listan som inte ingår i en aggregatfunktion också vara med i group by. Det som kan hända är att det blir slumpmässiga resultat.

T.ex om du har en tabell T

A B C
------
1 2 3
2 4 3

och ställer frågan

select a,c,max(b) from t group by C

Vilket värde förväntar du dig att du får för A?

Du bör läsa om group by på något annat ställe än i Mysqls dokumentation.

Det andra felet i Mysql som du råkar ut för är att de tillåter att du gör order by på något som du inte har med i select-listan då man har group by. Det är samma problem med detta som i fallet med group by.

Tillbaka till grundprobelemet. I mysql < 4.0 så får du dela upp frågan i två, en som räknar antal kommentarer och en som räknar betyg.

SELECT u.nickname,r.title,r.msg_date,
Count(v.vote),Sum(v.vote)
FROM tblRecord r INNER JOIN tblUser u 
ON u.user_ID = r.user_ID
LEFT JOIN tblVotes v 
ON v.object_ID = r.object_ID 
AND v.type_ID = 2
GROUP BY u.nickname,r.title,r.msg_date
ORDER BY r.msg_date DESC

och

SELECT u.nickname,r.title,r.msg_date,
Count(c.object_id)
FROM tblRecord r INNER JOIN tblUser u 
ON u.user_ID = r.user_ID
LEFT JOIN tblVotes v 
ON v.object_ID = r.object_ID 
AND v.type_ID = 2
GROUP BY u.nickname,r.title,r.msg_date
ORDER BY r.msg_date DESC

Annars så så skulle man skriva frågan med hjälp av skalära subselect

SELECT u.nickname,r.title,
(select count(*) from tblVotes 
 where type_id = 2 
     and object_ID = r.object_ID),
(select sum(votes) from tblVotes 
 where type_id = 2 
     and object_ID = r.object_ID),
(select count(*) from tblComments 
 where type_id = 2 
     and object_ID = r.object_ID)
Count(c.object_ID),Count(v.vote),Sum(v.vote)
FROM tblRecord r
INNER JOIN tblUser u ON u.user_ID = r.user_ID
 ORDER BY r.msg_date DESC

Då slipper man också alla tvetydigheter enligt ovan.