Using sub-query inside "IN" operator?

Question

I'm extending a basic shopping cart system and need a way to show all invoices in which a given product was purchased. I'm not sure a SELECT can be done this way, but I could a boost.

The problem is that invoice IDs correspond to shopping cart entries which identify the product IDs being purchased. I am querying for all purchases within a given category of products (p.categoryid).

SELECT i.id, i.name, i.totalprice, i.dateof
FROM invoices i
WHERE i.status > '1' AND i.id IN (
    SELECT c.invoice FROM maj_cart c, maj_products p WHERE c.pid = p.pid AND p.categoryid = '43'
)

Here is my database structure:

CREATE TABLE IF NOT EXISTS `maj_cart` (
`cid` int(10) NOT NULL auto_increment,
`pid` int(10) NOT NULL default '0',
`sessid` varchar(50) NOT NULL default '',
`dateof` int(10) NOT NULL default '0',
`price` decimal(10,2) NOT NULL,
`shipping` decimal(10,2) NOT NULL,
`discount` decimal(10,2) NOT NULL,
`quantity` int(10) NOT NULL default '0',
`total` decimal(10,2) NOT NULL,
`invoice` int(10) NOT NULL default '0',
`status` int(1) NOT NULL,
PRIMARY KEY  (`cid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

CREATE TABLE IF NOT EXISTS `maj_invoices` (
`id` int(10) NOT NULL auto_increment,
`dateof` int(10) NOT NULL default '0',
`userid` int(10) NOT NULL default '0',
`sessid` varchar(50) NOT NULL default '',
`name` varchar(255) NOT NULL default '',
`company` varchar(150) NOT NULL,
`email` varchar(255) NOT NULL default '',
`address1` varchar(255) NOT NULL default '',
`address2` varchar(255) NOT NULL default '',
`city` varchar(255) NOT NULL default '',
`state` char(3) NOT NULL default '',
`zip` varchar(10) NOT NULL default '',
`phone` varchar(40) NOT NULL default '',
`phone2` varchar(40) NOT NULL,
`instructions` text NOT NULL,
`recipmssg` text NOT NULL,
`promo` int(10) NOT NULL,
`discount` decimal(10,2) NOT NULL,
`totalprice` decimal(10,2) NOT NULL default '0.00',
`filename` varchar(20) NOT NULL,
`status` int(1) NOT NULL default '0',
`shipped` int(1) NOT NULL default '0',
`errorno` int(1) NOT NULL default '0',
`notes` text,
`source` int(5) NOT NULL default '0',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 PACK_KEYS=1 ;

CREATE TABLE IF NOT EXISTS `maj_products` (
`pid` int(10) NOT NULL auto_increment,
`itemnum` varchar(30) NOT NULL default '',
`filename` varchar(100) default NULL,
`original` varchar(255) default NULL,
`itemname` varchar(255) NOT NULL default '',
`descrip` text,
`summary` varchar(100) NOT NULL default '',
`categoryid` int(11) NOT NULL default '0',
`userid` int(10) NOT NULL default '0',
`dateadded` int(12) NOT NULL default '0',
`displayorder` int(10) NOT NULL default '0',
`price` decimal(10,2) NOT NULL default '0.00',
`shipping` decimal(10,2) NOT NULL default '0.00',
`instock` int(10) NOT NULL default '0',
`discount` int(10) NOT NULL,
`meta_keywords` text NOT NULL,
`meta_descrip` text NOT NULL,
PRIMARY KEY  (`pid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 PACK_KEYS=1 ;

I don't see a cid column in maj_invoices so how does it connect to maj_cart? — Ariel
– Ariel, Commented Sep 23, 2011 at 21:28
i.status > '1' status is an int, don't compare it to a string. — Ariel
– Ariel, Commented Sep 23, 2011 at 21:30

Glen Solsberry · Accepted Answer · 2011-09-26 14:32:22Z

2

The suggested method would be just a normal join:

SELECT i.id, i.name, i.totalprice, i.dateof
FROM invoices i
INNER JOIN maj_cart c ON c.invoice = i.id
INNER JOIN maj_products p ON c.pid = p.pid
WHERE p.category_id = 43
AND i.status > '1'

edited Sep 26, 2011 at 14:32

answered Sep 23, 2011 at 21:26

Glen Solsberry

12.4k16 gold badges74 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tanoro Over a year ago

I had a fixed a few syntax errors there (i.e. two WHERE clauses), but I got it. Thanks.

Collectives™ on Stack Overflow

Using sub-query inside "IN" operator?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related