[RESOLVED] Query help

[webweever]

Can someone tell me why this query is selecting all records not just those with the user_id = $id?

"SELECT SUM(item.itemtotalprice)
	FROM item
	JOIN lists
	ON item.list_name = lists.name
	WHERE item.user_id = $id AND lists.name = '$listname'"

This is my DB structure:

CREATE TABLE `item` (
  `item_id` int(11) NOT NULL auto_increment,
  `user_id` int(20) NOT NULL,
  `list_name` varchar(30) collate latin1_general_ci NOT NULL,
  `date` date NOT NULL,
  `store` varchar(30) collate latin1_general_ci NOT NULL,
  `item` varchar(30) collate latin1_general_ci NOT NULL,
  `itemprice` decimal(10,2) unsigned NOT NULL,
  `itemnumber` tinyint(20) NOT NULL,
  `itemtotalprice` decimal(10,2) NOT NULL,
  PRIMARY KEY  (`item_id`)
);

CREATE TABLE `lists` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(32) collate latin1_general_ci NOT NULL,
  `dt` date NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`)
) ;

Interesting note: If I use this query it only pulls those records with user_id=$id but if 2 records have the same itemtotalprice it only pulls one.

("SELECT SUM(DISTINCT item.itemtotalprice)
		FROM item
		JOIN lists
		ON item.list_name = lists.name
		WHERE item.user_id = $id AND lists.name = '$listname'");

Any ideas?

[webweever]

Solved, I'm an idiot. I was making it more difficult than it needed to be.

I used this:

("SELECT SUM(itemtotalprice)
	FROM item
	WHERE user_id = $id AND list_name = '$listname'");

webweever;10980503 wrote:

Can someone tell me why this query is selecting all records not just those with the user_id = $id?

"SELECT SUM(item.itemtotalprice)
	FROM item
	JOIN lists
	ON item.list_name = lists.name
	WHERE item.user_id = $id AND lists.name = '$listname'"

This is my DB structure:

CREATE TABLE `item` (
  `item_id` int(11) NOT NULL auto_increment,
  `user_id` int(20) NOT NULL,
  `list_name` varchar(30) collate latin1_general_ci NOT NULL,
  `date` date NOT NULL,
  `store` varchar(30) collate latin1_general_ci NOT NULL,
  `item` varchar(30) collate latin1_general_ci NOT NULL,
  `itemprice` decimal(10,2) unsigned NOT NULL,
  `itemnumber` tinyint(20) NOT NULL,
  `itemtotalprice` decimal(10,2) NOT NULL,
  PRIMARY KEY  (`item_id`)
);

CREATE TABLE `lists` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(32) collate latin1_general_ci NOT NULL,
  `dt` date NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`)
) ;

Interesting note: If I use this query it only pulls those records with user_id=$id but if 2 records have the same itemtotalprice it only pulls one.

("SELECT SUM(DISTINCT item.itemtotalprice)
		FROM item
		JOIN lists
		ON item.list_name = lists.name
		WHERE item.user_id = $id AND lists.name = '$listname'");

Any ideas?