0

Suppose we have the following schema:

CREATE TABLE customers(
    id INTEGER PRIMARY KEY, 
    name TEXT
);
CREATE TABLE items(
    id INTEGER PRIMARY KEY, 
    name TEXT
);
CREATE TABLE customers_items(
    customerid INTEGER, 
    itemid INTEGER, 
    FOREIGN KEY(customerid) REFERENCES customers(id),
    FOREIGN KEY(itemid) REFERENCES items(id)
);

Now we insert some example data:

INSERT INTO customers(name) VALUES ('John');
INSERT INTO customers(name) VALUES ('Jane');

INSERT INTO items(name) VALUES ('duck');
INSERT INTO items(name) VALUES ('cake');

Let's assume that John and Jane have id's of 1 and 2 and duck and cake also have id's of 1 and 2. Let's give a duck to John and both a duck and a cake to Jane.

INSERT INTO customers_items(customerid, itemid) VALUES (1, 1);
INSERT INTO customers_items(customerid, itemid) VALUES (2, 1);
INSERT INTO customers_items(customerid, itemid) VALUES (2, 2);

Now, what I want to do is to run two types of queries:

  1. Select names of customers who have BOTH a duck and a cake (should return 'Jane' only).
  2. Select names of customers that have a duck and DON'T have a cake (should return 'John' only).
user1205255
  • 139
  • 1
  • 6
  • 1
    See: http://stackoverflow.com/questions/29012455/find-the-sids-of-the-suppliers-who-supply-every-part/29013311#29013311. There are numerous walkthroughs of this problem out there, as some version of it is usually the example given to explain the query that solves it. Adapting it to answer your second question should be easy once you have grasped the answer to the first. – Paul Griffin May 01 '15 at 16:22
  • It will be more fun and better for your learning if you try to figure it yourself. You can use either EXISTS or JOIN. – Vojtěch Dohnal May 01 '15 at 16:45

1 Answers1

1

For the two type of queries listed, you could use the EXISTS clause. Below is an example query using the exists clause:

SELECT cust.name 
from customers AS cust
WHERE EXISTS (
     SELECT 1
     FROM items
     INNER JOIN customers_items ON items.id = customers_items.itemid
     INNER JOIN customers on customers_items.customerid = cust.id
     WHERE items.name = 'duck')
AND NOT EXISTS (
     SELECT 1
     FROM items
     INNER JOIN customers_items ON items.id = customers_items.itemid
     INNER JOIN customers on customers_items.customerid = cust.id
     WHERE items.name = 'cake')

Here is a working example: http://sqlfiddle.com/#!6/3d362/2

Sam Abushanab
  • 492
  • 3
  • 13