Reputation: 85

Find all rows in one table that are in two other tables

I'm struggling with how to do the query for this. I have three tables...ingredients, recipes, and stores. I'm trying to build a query that will tell me what recipes I can make from the ingredients available at a store. My tables are:


    mysql> SELECT * FROM ingredients;
    +---------+
    | id      |
    +---------+
    | apple   |
    | beef    |
    | cheese  |
    | chicken |
    | eggs    |
    | flour   |
    | milk    |
    | pasta   |
    | sugar   |
    | tomato  |
    +---------+
    10 rows in set (0.00 sec)

    mysql> SELECT * FROM stores;
    +----------+------------+
    | name     | ingredient |
    +----------+------------+
    | target   | apple      |
    | target   | chicken    |
    | target   | flour      |
    | target   | milk       |
    | target   | sugar      |
    | wal-mart | beef       |
    | wal-mart | cheese     |
    | wal-mart | flour      |
    | wal-mart | milk       |
    | wal-mart | pasta      |
    | wal-mart | tomato     |
    +----------+------------+
    11 rows in set (0.00 sec)

    mysql> SELECT * FROM recipes;
    +---------------+------------+
    | name          | ingredient |
    +---------------+------------+
    | apple pie     | apple      |
    | apple pie     | flour      |
    | apple pie     | milk       |
    | apple pie     | sugar      |
    | cheeseburger  | beef       |
    | cheeseburger  | cheese     |
    | cheeseburger  | flour      |
    | cheeseburger  | milk       |
    | fried chicken | chicken    |
    | fried chicken | flour      |
    | spaghetti     | beef       |
    | spaghetti     | pasta      |
    | spaghetti     | tomato     |
    +---------------+------------+
    13 rows in set (0.00 sec)

    mysql>

Given the above I want to build a query where I give it the store name (say wal-mart for this example) and it produces a list of the recipes I can make from the ingredients available at wal-mart (cheeseburger & spaghetti).

Here's the SQL to create these tables:


    CREATE TABLE IF NOT EXISTS ingredients (
      id varchar(32) NOT NULL,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO ingredients (id) VALUES
    ('apple'),
    ('beef'),
    ('cheese'),
    ('chicken'),
    ('eggs'),
    ('flour'),
    ('milk'),
    ('pasta'),
    ('sugar'),
    ('tomato');

    CREATE TABLE IF NOT EXISTS recipes (
      `name` varchar(32) NOT NULL,
      ingredient varchar(32) NOT NULL,
      PRIMARY KEY (`name`,ingredient)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO recipes (`name`, ingredient) VALUES
    ('apple pie', 'apple'),
    ('apple pie', 'flour'),
    ('apple pie', 'milk'),
    ('apple pie', 'sugar'),
    ('cheeseburger', 'beef'),
    ('cheeseburger', 'cheese'),
    ('cheeseburger', 'flour'),
    ('cheeseburger', 'milk'),
    ('fried chicken', 'chicken'),
    ('fried chicken', 'flour'),
    ('spaghetti', 'beef'),
    ('spaghetti', 'pasta'),
    ('spaghetti', 'tomato');

    CREATE TABLE IF NOT EXISTS stores (
      `name` varchar(32) NOT NULL,
      ingredient varchar(32) NOT NULL,
      UNIQUE KEY NAME_INGREDIENT (`name`,ingredient)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO stores (`name`, ingredient) VALUES
    ('target', 'apple'),
    ('target', 'chicken'),
    ('target', 'flour'),
    ('target', 'milk'),
    ('target', 'sugar'),
    ('wal-mart', 'beef'),
    ('wal-mart', 'cheese'),
    ('wal-mart', 'flour'),
    ('wal-mart', 'milk'),
    ('wal-mart', 'pasta'),
    ('wal-mart', 'tomato');

Upvotes: 1

Answers (4)

joop

Reputation: 4503

(Remodelled the data a bit to use only numerical keys)

CREATE TABLE IF NOT EXISTS ingredients (
  ing_id INTEGER NOT NULL PRIMARY KEY
  , ing_name varchar(32) NOT NULL
) ;

INSERT INTO ingredients (ing_id, ing_name) VALUES
(1, 'apple' ),
(2, 'beef' ),
(3, 'cheese' ),
(4, 'chicken' ),
(5, 'eggs' ),
(6, 'flour' ),
(7, 'milk' ),
(8, 'pasta' ),
(9, 'sugar' ),
(10, 'tomato' );

CREATE TABLE IF NOT EXISTS recipes (
  rec_id INTEGER NOT NULL PRIMARY KEY
  , rec_name varchar(32) NOT NULL
 );

INSERT INTO recipes (rec_id, rec_name) VALUES
(1, 'apple pie' ) ,
(2, 'cheeseburger' ) ,
(3, 'fried chicken' ) ,
(4, 'spaghetti' ) ;

CREATE TABLE IF NOT EXISTS recipe_ingredient (
  rec_id INTEGER NOT NULL REFERENCES recipes(rec_id)
  , ing_id INTEGER NOT NULL REFERENCES ingredients (ing_id)
  , PRIMARY KEY (rec_id, ing_id)
 );

INSERT INTO recipe_ingredient (rec_id, ing_id) VALUES
(1, 1), (1, 6), (1, 7), (1, 9),
(2, 2), (2, 3), (2, 6), (2, 7),
(3, 4), (3, 6), (4, 2),
(4, 8), (4, 10);
8), (4, 10);

CREATE TABLE IF NOT EXISTS stores (
  sto_id INTEGER NOT NULL PRIMARY KEY
  , sto_name varchar(32) NOT NULL
 );

INSERT INTO stores (sto_id, sto_name) VALUES
(1, 'target' ),
(2, 'wal-mart' );

CREATE TABLE IF NOT EXISTS store_ingredient (
  sto_id INTEGER NOT NULL REFERENCES stores(sto_id)
  , ing_id INTEGER NOT NULL REFERENCES ingredients(ing_id)
  , PRIMARY KEY (sto_id, ing_id)
 );

INSERT INTO store_ingredient (sto_id, ing_id ) VALUES
(1, 1), (1, 4), (1, 6), (1, 7), (1, 9),
(2, 2), (2, 3), (2, 6), (2, 7), (2, 8), (2, 10)
        ;

Query to find all recipes for which all the ingredients can be supplied by wal-mart.

SELECT r.rec_name
        -- All the recipes
FROM recipes r
WHERE NOT EXISTS (
        SELECT *
        -- ... that do not contain any ingredient
        FROM recipe_ingredient ri
        WHERE ri.rec_id = r.rec_id
        AND NOT EXISTS (
                SELECT *
                -- ... that cannot be provided by wal-mart.
                FROM store_ingredient si
                JOIN stores s ON s.sto_id = si.sto_id
                WHERE si.ing_id = ri.ing_id
                 AND s.sto_name = 'wal-mart'
                )
        );

Upvotes: 0

spencer7593

Reputation: 108380

Here's one way to get the specified result.

If you want to check just one or two recipes, then include a WHERE clause in the inline view query (commented out in the query below, which returns all recipes.) If you want to check just one or two stores, include a predicate in the outer query (commented out in the query below.)

 SELECT s.name AS store_name
      , r.name AS recipe_name
      , i.ri_count
  FROM  ( SELECT ri.name
               , COUNT(DISTINCT ri.ingredient) AS ri_count
            FROM recipes ri
        -- WHERE ri.name IN ('fried chicken','spaghetti')
           GROUP
              BY ri.name
         ) i
   JOIN recipes r
     ON r.name = i.name
   LEFT
   JOIN stores s
     ON s.ingredient = r.ingredient
 -- AND s.name IN ('target','wal-mart')
  GROUP
     BY s.name
      , r.name
 HAVING COUNT(DISTINCT s.ingredient) = i.ri_count

Upvotes: 1

fagace

Reputation: 126

Hi first of all I want to tell you that it's better that you improve your model. Try to normalize it. In the other hand If I not wrong you need to do something like this:

SELECT GROUP_CONCAT(r.name SEPARATOR " & ") recipients
FROM stores s
JOIN ingredients i ON s.ingredient=i.id
JOIN recipes r ON r.ingredient=i.id
WHERE s.name="wal-mart"
GROUP BY r.ingredient

Play with this http://sqlfiddle.com/#!2/3c763/27

Upvotes: 0

slavoo

Reputation: 6076

try this:

SELECT r.name FROM recipes r          
GROUP BY r.name
HAVING COUNT(*) = (SELECT COUNT(*) 
     FROM recipes r2 INNER JOIN stores s 
        ON r2.ingredient = s.ingredient AND s.name = 'wal-mart'
     WHERE r.name = r2.name)

fiddle demo

Upvotes: 3

Find all rows in one table that are in two other tables

Answers (4)

Related Questions