TalkPHP - Mysql problem, most likely related to left join

TalkPHP (http://www.talkphp.com/forums.php)

- MySQL & Databases (http://www.talkphp.com/mysql-databases/)

- - Mysql problem, most likely related to left join (http://www.talkphp.com/mysql-databases/1841-mysql-problem-most-likely-related-left-join.html)

Mysql problem, most likely related to left join

I am making a polling script, I have it able to get the top 3 items voted for. I am trying to left join the item data with the result data with this query

sql Code:

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

(Thanks Wildhoney for the reformatting)
It tels me that votes.poll_id doesn't exist. That is simply not true, I can call votes.poll_id in a separate query and it comes up.

Hmmm. I don't see any immediate issues standing out. Could you possibly attach your MySQL dump with dummy data so we can emulate and ponder?

I reformatted the SQL if anybody else wishes to have a stab at it.

sql Code:

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

Personal preference really on the formatting! :-)

Here is the dump, im running mysql 4.1.21

Code:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";







CREATE TABLE `ip` (

  `id` int(9) NOT NULL auto_increment,

  `ip` varchar(15) NOT NULL,

  `poll_id` int(9) NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;







INSERT INTO `ip` VALUES(12, '76.217.61.249', 1);

INSERT INTO `ip` VALUES(11, '99.132.134.126', 1);





CREATE TABLE `items` (

  `id` int(9) NOT NULL auto_increment,

  `poll_id` int(9) NOT NULL,

  `name` varchar(255) NOT NULL,

  `image` text NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;





INSERT INTO `items` VALUES(1, 1, 'item 1', 'images/klickers/krew18_franklin.jpg');

INSERT INTO `items` VALUES(2, 1, 'other', 'images/klickers/krew18_franklin.jpg');

INSERT INTO `items` VALUES(3, 1, 'pen4', 'images/klickers/krew18_franklin.jpg');

INSERT INTO `items` VALUES(4, 1, 'pen3', 'images/klickers/krew18_franklin.jpg');





CREATE TABLE `polls` (

  `id` int(9) NOT NULL auto_increment,

  `date` int(10) NOT NULL,

  `name` varchar(255) NOT NULL,

  `active` int(1) NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;





INSERT INTO `polls` VALUES(1, 112111111, 'Dev poll', 1);





CREATE TABLE `votes` (

  `id` int(9) NOT NULL auto_increment,

  `poll_id` int(9) NOT NULL,

  `item_id` int(9) NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=43 ;



INSERT INTO `votes` VALUES(42, 1, 4);

INSERT INTO `votes` VALUES(41, 1, 3);

INSERT INTO `votes` VALUES(40, 1, 2);

INSERT INTO `votes` VALUES(39, 1, 3);

INSERT INTO `votes` VALUES(38, 1, 2);

INSERT INTO `votes` VALUES(37, 1, 1);

INSERT INTO `votes` VALUES(36, 1, 3);

INSERT INTO `votes` VALUES(35, 1, 2);

INSERT INTO `votes` VALUES(34, 1, 1);

Hmm. That works perfectly for me. Perhaps it's an issue in MySQL 4.1?

Don't know, I've already given up and built it using multiple queries. It's a shame really, I have to use 3 more now. Il have to switch servers when I have the chance, but that causes hours of downtime.

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;

Took a little tweaking, but this works.

Code:

SELECT

    votes.poll_id AS votes_pollid,

    COUNT(item_id) AS count,

    items.* 

FROM       

    votes 

LEFT JOIN 

    items

ON  

    votes.item_id = items.id 

WHERE   

    votes.poll_id = 1 

GROUP BY 

    votes.item_id

ORDER BY 

    count DESC 

LIMIT   

    3;

Thanks!