Mysql problem, most likely related to left join

Village Idiot · 01-02-2008, 01:29 AM

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.

Wildhoney · 01-02-2008, 02:23 AM

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!

Village Idiot · 01-02-2008, 02:28 AM

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);

Wildhoney · 01-02-2008, 01:12 PM

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

Village Idiot · 01-02-2008, 03:43 PM

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.

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;

Village Idiot · 01-04-2008, 06:09 AM

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!