MYSQL Sub Select Not Working

Question

I am trying to run two queries in one. When I run both queries seperatley, they work and provide me with the relevant information I want. When I try to combine them I seem to be going wrong somewhere. Is there something blatantly obvious that I am doing wrong?

 SELECT 
   pd.product_id, b.product_id, basket_qty, 
   product_name, product_price, product_image, 
   pd.category_id, basket_id
 (SELECT 
   pd.product_id, b.product_id, 
   basket_session_id, 
   SUM(product_price) AS subtotal 
  FROM 
   basket b, product pd 
  WHERE 
   basket_Session_id = '9htt961lpa1kqieogd5ig5ff93' AND 
   b.product_id = pd.product_id)
 FROM 
   basket b, product pd, department dep
 WHERE 
   basket_session_id = '9htt961lpa1kqieogd5ig5ff93' 
   AND b.product_id = pd.product_id 
   AND dep.department_id = pd.category_id

Table Structure -

CREATE TABLE IF NOT EXISTS `basket` (
  `basket_id` int(10) unsigned NOT NULL auto_increment,
  `product_id` int(10) unsigned NOT NULL,
  `basket_qty` int(10) unsigned NOT NULL default '1',
  `basket_session_id` char(32) NOT NULL default '',
  `basket_date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`basket_id`),
  KEY `product_id` (`product_id`),
  KEY `basket_session_id` (`basket_session_id`)
)

CREATE TABLE IF NOT EXISTS `product` (
  `product_id` int(10) unsigned NOT NULL auto_increment,
  `category_id` int(10) unsigned NOT NULL,
  `department_name` varchar(100) NOT NULL,
  `product_name` varchar(100) NOT NULL default '',
  `product_description` text NOT NULL,
  `product_price` decimal(7,2) NOT NULL default '0.00',
  `product_qty` smallint(5) unsigned NOT NULL default '0',
  `product_size` text NOT NULL,
  `product_image` varchar(200) default NULL,
  `product_date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`product_id`),
  UNIQUE KEY `product_name` (`product_name`),
  KEY `category_id` (`category_id`)
)


CREATE TABLE IF NOT EXISTS `department` (
  `department_id` int(10) unsigned NOT NULL auto_increment,
  `department_parent_id` int(11) NOT NULL default '0',
  `name` varchar(50) NOT NULL default '',
  `description` varchar(200) NOT NULL default '',
  `image` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`department_id`),
  UNIQUE KEY `name` (`name`),
  KEY `department_parent_id` (`department_parent_id`)
)

I am trying to pull product information based on a "basket" session for an ecommerce application. As a basket can contain more than one product I want to return the SUM of total value from all products.

This simply is not valid Syntax. Giving us an idea wht you want to select from what table structure will help us find a solution for you — Eugen Rieck, Dec 31 '11 at 14:14
IT appears you're trying to get a subtotal for the results one way to achieve this is to use [with rollup](http://forums.mysql.com/read.php?12,106959,106959), another way would be to use variables to keep a [running total](http://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql), and a final way would be to use a correlated subquery — xQbert, Dec 31 '11 at 14:18
Can you narrow down the issue? Remove all those fields that I'm sure aren't necessary to ask the question. — Lightness Races in Orbit, Dec 31 '11 at 14:26

Jules · Answer 1 · 2011-12-31T14:22:26.690

Make sure you add a comma right before your subselect..

SELECT 
   pd.product_id, b.product_id, basket_qty, 
   product_name, product_price, product_image, 
   pd.category_id, basket_id,  
 (SELECT ...

Other than that your query doesn't look valid to me. What exactly are you trying to achieve? I am sure there are way better queries than the one you just wrote.

So I'm not sure what you are trying to achieve and I don't know how your database looks like but modify this query to fulfill your needs. It should be a good start for you:

SELECT pd.product_id, b.product_id, b.basket_qty,
       pd.product_name, pd.product_price, pd.product_image,
       pd.category_id, b.basket_id, b.basket_session_id, 
       SUM (pd.product_price) AS subtotal
FROM product pd
JOIN basket b ON b.product_id = pd.product_id
JOIN department dep ON dep.department_id = pd.category_id
WHERE b.basket_session_id = '9htt961lpa1kqieogd5ig5ff93'
GROUP BY product_id

Also notice that you use the department table but you never select any field from it or anything. So it seems to be useless here. I just added it in my query in case you want to improve it later on.

score -1 · Accepted Answer · answered Dec 31 '11 at 14:26

-1

This won't work as the sub-query is effectively a column returned in the overall query, however, your sub-query returns more than one column. You could remove the extra columns though and do this, which should work.

 SELECT pd.product_id, b.product_id, basket_qty
      , product_name, product_price, product_image
      , pd.category_id, basket_id
        -- moved from sub-query
      , basket_session_id 
      , ( SELECT SUM(product_price) 
            FROM basket b, product pd 
           WHERE basket_Session_id = '9htt961lpa1kqieogd5ig5ff93' 
             AND b.product_id = pd.product_id ) AS subtotal
  FROM basket b, product pd, department dep
 WHERE basket_session_id = '9htt961lpa1kqieogd5ig5ff93' 
   AND b.product_id = pd.product_id 
   AND dep.department_id = pd.category_id

answered Dec 31 '11 at 14:26

Ben

51,770
36
127
149

Subqueries are pretty much an anti-pattern. And you aren't using a `GROUP BY` with your `SUM()`. – Jules Dec 31 '11 at 14:30
That has done it! I didn't realize that was the problem. It makes sense now as I think I was returning about 4 columns for the sub... Thanks – Dec 31 '11 at 14:31
@Jules I don't need a group by as I've not select any other columns. Secondly, it's a bit rude to down-vote an alternative answer to one's own. – Ben Dec 31 '11 at 14:34
@Jules: why are subqueries an *anti-pattern*? They are the corner stone of the SQL language. – Dec 31 '11 at 14:38
@Ben Pretty sure you had more columns selected before. Anyway this subquery is still an anti-pattern. The query is clearly not optimized and is a disgrace for any SQL developer. Even if this query does what is requested by Rory, it's not contributing to the performance or to his knowledge. – Jules Dec 31 '11 at 14:38
@a_horse_with_no_name Read this query and question yourself if this could not be optimized by not making use of the subquery. – Jules Dec 31 '11 at 14:42
1

@Jules, you can see the answer hasn't been edited. I can **prove** with trace files in my DB that sub-queries **can** be faster and better for the DB in every respect; I spent an hour on one query 2 weeks ago as, in that instance, it made no sense to me. Making a sweeping statement does not make it correct. – Ben Dec 31 '11 at 14:42
@Jules: I agree that in this case a sub-query is not needed. But stating that sub-queries in general are an anti-pattern (and thus bad) is simply wrong. – Dec 31 '11 at 15:28

MYSQL Sub Select Not Working

2 Answers2