many-to-many and many-to-many intersections

Question

Say I have a database that has people, grocery stores, and items you can buy in the store, like so:

Stores               People                Foods
-----------------    ------------------    ------------------
| id |   name   |    | id |   name    |    | id |   name    |
-----------------    ------------------    ------------------
| 1  |  Giant   |    | 1  | Jon Skeet |    | 1  | Tomatoes  |
| 2  |  Vons    |    | 2  | KLee1     |    | 2  | Apples    |
| 3  | Safeway  |    ------------------    | 3  | Potatoes  |
-----------------                          ------------------

I have an additional table which keep track of which stores sell what:

Inventory
--------------------
| store_id| food_id|
--------------------
| 1       | 1      |
| 1       | 2      |
| 2       | 1      |
| 3       | 1      |
| 3       | 2      |
| 3       | 3      |
--------------------

And I have another table that has shopping lists on it

Lists
---------------------
| person_id| food_id|
---------------------
| 1        | 1      |
| 1        | 2      |
| 1        | 3      |
| 2        | 1      |
| 2        | 3      |
---------------------

My question is, given a person, or their id, what is the best way to figure out what stores they can go to so they will get everything on their list. Is there a pattern for these types of computations in MySQL?

My attempt (very ugly and messy) is something like:

-- Given that _pid is the person_id we want to get the list of stores for.

SELECT stores.name, store_id, num, COUNT(*) AS counter
FROM lists
    INNER JOIN inventory 
        ON (lists.food_id=inventory.food_id)
    INNER JOIN (SELECT COUNT(*) AS num
            FROM lists WHERE person_id=_pid 
            GROUP BY person_id) AS T
    INNER JOIN stores ON (stores.id=store_id)
WHERE person_id=_pid 
GROUP BY store_id
HAVING counter >= num;

Thanks for your time!

Edit SQL Fiddle with Data

thanks for posting the table DDL/DML but it would be even better if you created a working model on [SQL Fiddle](http://sqlfiddle.com/) — Taryn, Sep 20 '12 at 23:16

John Woo · Accepted Answer · 2012-09-21T00:39:18.090

3

If I were to solved the problem, I'll join the four tables with their linking column (specifically the foreign keys) then a subquery on the HAVING clause to count the number of items on the list for each person. Give this a try,

SET @personID := 1;

SELECT  c.name
FROM    Inventory a
        INNER JOIN Foods b
            ON a.food_id = b.id
        INNER JOIN Stores c
            ON a.store_id = c.id
        INNER JOIN Lists d
            ON d.food_id = b.id
WHERE   d.person_id = @personID
GROUP BY c.name
HAVING   COUNT(DISTINCT d.food_id) =
     (
        SELECT COUNT(*)
        FROM Lists
        WHERE person_ID = @personID
     )

SQLFiddle Demo

edited Sep 21 '12 at 00:39

answered Sep 21 '12 at 00:29

John Woo

258,903
69
498
492

You should be able to do the whole job, selecting all the people who can satisfy their shopping lists as a single store along with the name of the store where they can do so without needing the `@personID` notation. – Jonathan Leffler Sep 21 '12 at 00:48
@JonathanLeffler I was just answering the OP what the OP wants `My question is, given a person, or their id...`. Anyway, I will just update the answer :) Thanks. – John Woo Sep 21 '12 at 00:52
Is there a corner case here? What if the person has nothing on their list? They can go to any store in that case. Would it be best to just have an if statement for this case? – KLee1 Sep 21 '12 at 19:45

user1674979 · Answer 2 · 2012-09-21T01:07:30.497

1

@JohnWoo: why DISTINCT?

Another one...

SET @pid=2;

SELECT store_id, name
FROM inventory 
  JOIN lists ON inventory.food_id=lists.food_id
  JOIN stores ON store_id=stores.id
WHERE person_id=@pid
GROUP BY store_id
HAVING COUNT(*)=(
  SELECT COUNT(*)
  FROM lists 
  WHERE person_id=@pid
);

edited Sep 21 '12 at 01:07

answered Sep 21 '12 at 00:49

user1674979

41
3

many-to-many and many-to-many intersections

2 Answers2

SQLFiddle Demo

Linked