Nested SQL statement (simple)

Question

I am trying to run a query that gets the ids of customers (cid) who ordered both product p01 and p07.

Here is what I though would work, but it returns nothing:

SELECT cid
FROM customers
WHERE cid IN (SELECT cid
              FROM orders
              WHERE pid IN (SELECT pid
                            FROM products
                            WHERE pid = 'p01'
                            AND pid = 'p07'));

you wouldn't by any chance have your schema off-hand would you? — Drew, Feb 18 '16 at 17:43
How can one `pid` be both, `p01` AND `p07`, at the same time? use 'OR'. The `IN (... values... )` can be thought of as a list of `OR` tests. — Ryan Vincent, Feb 18 '16 at 17:44

Fuzzy · Accepted Answer · 2016-02-18T20:22:24.447

Your original query will not return any rows because

 SELECT pid 
 FROM products
 WHERE pid = 'p01' AND pid = 'p07';

Will always return no rows because the condition WHERE pid = 'p01' AND pid = 'p07' will never be met.

What you should do is:

SELECT cid
FROM customers
WHERE cid IN (SELECT cid
              FROM orders
              WHERE pid IN (SELECT pid
                            FROM products
                            WHERE pid IN ('p01', 'p07')));

You can also use a JOIN:

SELECT DISTINCT C.cid
FROM   customers AS C 
       INNER JOIN 
       orders AS O ON O.cid = C.cid
       INNER JOIN 
       products AS P ON P.pid = O.pid
WHERE   P.pid IN ('p01', 'p07')

If you do not want to use DISTINCT then you can do this:

SELECT  C.cid
FROM    customers AS C 
WHERE   EXISTS  (SELECT O.cid 
              FROM   orders AS O INNER JOIN 
                     products AS P ON P.pid = O.pid
              WHERE  P.pid IN ('p01', 'p07') AND O.cid = C.cid)

based on your comment " It returns c001, c004, and c006, but customer c004 had only ordered one of the products (p01) not both (p07 and p01)" I think you will need to do this:

SELECT  C.cid
FROM    customers AS C 
WHERE   EXISTS  (SELECT O.cid 
              FROM   orders AS O INNER JOIN 
                     products AS P ON P.pid = O.pid
              WHERE  P.pid IN ('p07') AND O.cid = C.cid) AND 
        EXISTS (SELECT O.cid 
              FROM   orders AS O INNER JOIN 
                     products AS P ON P.pid = O.pid
              WHERE  P.pid IN ('p01')  AND O.cid = C.cid )

I haven't really delved into JOIN yet. The first example you gave seems right, but it still returns a cid that has only one of the products (pid) instead of both. — Stephen Bohner, Feb 18 '16 at 20:07
Yes it does. It returns c001, c004, and c006, but customer c004 had only ordered one of the products (p01) not both (p07 and p01). — Stephen Bohner, Feb 18 '16 at 20:16
@StephenBohner see the latest edit to my answer that should do it — Fuzzy, Feb 18 '16 at 20:22

score 0 · Answer 2 · answered Feb 18 '16 at 17:44

0

A pid is never both 'p01' and 'p07'. Use OR, or alternatively just IN:

SELECT DISTINCT customers.cid
FROM customers
JOIN orders ON orders.cid = customers.cid
WHERE orders.pid IN ('p01', 'p07')

answered Feb 18 '16 at 17:44

what if pid values 'p01' and/or 'P07' do not exist in the product table? – Fuzzy Feb 18 '16 at 17:50
@KamranFarzami then it's just going to return an empty result set – Ven Feb 19 '16 at 12:41
@Ven how the solution above has to reference to the product table. – Fuzzy Feb 19 '16 at 12:43
you mean `join`s? see http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join – Ven Feb 19 '16 at 12:45

Nested SQL statement (simple)

2 Answers2