TalkPHP - View Single Post - Mysql problem, most likely related to left join

macov · 01-03-2008, 03:39 AM

Shouldn't be item_id removed from "GROUP BY" and "SELECT"?. When you try to COUNT item_id, you should list all columns (e.g., votes.poll_id votes_pollid, itmes.* in the GROUP BY (with an exception for item_id). At least it would work in Oracle...

Code:

SELECT
    votes.poll_id AS votes_pollid,
    COUNT(item_id) AS count,
    items.* 
FROM       
    votes 
LEFT JOIN 
    items
ON  
    votes.id = items.poll_id 
WHERE   
    votes.poll_id = 1 
GROUP BY 
    votes.poll_id,
    items.id,items.poll_id,items.name,items.image
ORDER BY 
    count DESC 
LIMIT   
    3;

01-03-2008, 03:39 AM	#6 (permalink)
macov The Wanderer Join Date: Nov 2007 Posts: 9 Thanks: 0	Shouldn't be item_id removed from "GROUP BY" and "SELECT"?. When you try to COUNT item_id, you should list all columns (e.g., votes.poll_id votes_pollid, itmes.* in the GROUP BY (with an exception for item_id). At least it would work in Oracle... Code: SELECT votes.poll_id AS votes_pollid, COUNT(item_id) AS count, items.* FROM votes LEFT JOIN items ON votes.id = items.poll_id WHERE votes.poll_id = 1 GROUP BY votes.poll_id, items.id,items.poll_id,items.name,items.image ORDER BY count DESC LIMIT 3;