CODEWITHSUNDEEP
mysql
user1124378
user1124378

Reputation:

MYSQL Sub Select Not Working

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.

Upvotes: 2

Views: 286

Answers (2)

Ben
Ben

Reputation: 52863

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

Upvotes: -1

Jules
Jules

Reputation: 7223

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.

Upvotes: 3

Related Questions