CODEWITHSUNDEEP
sqlpostgresql
codering
codering

Reputation: 55

SQL query to find records if and only if join table contains all of a list of values

I have two tables that are joined by an association table. The requirement is to find only records in price table that include all of the menu codes in a list and only those in the list. The data (simplified) looks like this

price Table
price_id | site
----------------
1001     | 150
1002     | 151
1003     | 152
1004     | 153

menu Table
menu_id  | code   
------------------
5001     | 10000
5002     | 10001
5003     | 10002
5004     | 10003

price_menu Table
price_id | menu_id
------------------
1001     | 5002
1002     | 5002
1002     | 5003
1003     | 5002
1003     | 5003
1003     | 5004

Joining price and menu through price_menu, you would get

price joined with price_menu joined with menu
price_id | site  |  menu_id  | code
------------------------------------
1001     | 150   |  5002     | 10001
1002     | 151   |  5002     | 10001
1002     | 151   |  5003     | 10002
1003     | 152   |  5002     | 10001
1003     | 153   |  5003     | 10002
1003     | 153   |  5004     | 10003

So, if I wanted to find prices that are associated with menu.code=10001 and menu.code=10002, and only those codes, I should get the record with price_id=1002 only. The record with price_id=1001 has only one of the two menu.codes and the record with price_id=1003 has and addition menu. Therefore, only record(s) for price_id=1002 should be returned.

I came up with this query. However, the problem is that there could be many menus associated with a price and it would get too large and probably slow as every menu you add to the query you need another subquery.

SELECT * FROM (SELECT *,
  (SELECT code AS mycode FROM price AS pc1 
   INNER JOIN price_menu AS pm1 ON pc1.id=pm1.price_id 
   JOIN menu AS m1 ON pm1.menu_id=m1.id
   WHERE pc1.id=pc.id
   AND m1.code = '1571403743330'
) AS data1,
  (SELECT code AS mycode FROM price AS pc2 
   INNER JOIN price_menu AS pm2 ON pc.id=pm2.price_id 
   JOIN menu AS m2 ON pm2.menu_id=m2.id
   WHERE pc2.id=pc.id
   AND m2.code = '1571403754395'
) AS data2
FROM price AS pc ) AS outerquery
WHERE data1 = '1571403743330'
AND data2 = '1571403754395'

This will produce one row per price found. However, there is no requirement that it be a single row. Can anyone suggest an alternative that would not require an additional subquery per menu?

Upvotes: 0

Views: 1286

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

I would approach this using aggregation and having:

select pm.price_id
from menu m join
     price_menu pm
     on m.menu_id = pm.menu_id
group by pm.price_id
having count(distinct pm.code) = 2 and
       count(distinct case when pm.code in (1001, 1002) then pm.code end)) = 2;  -- number of things you are looking for

Note: If there are no duplicate codes for a given price id, then use count() rather than count(distinct).

In Postgres, you can use arrays for this information:

select pm.price_id
from menu m join
     price_menu pm
     on m.menu_id = pm.menu_id
group by pm.price_id
having count(distinct pm.code) = array_length(array[1, 2]) and
       count(distinct case when pm.code = any(array[1, 2]) then pm.code end)) = array_length(array[1, 2]);

This makes it easier to pass the array in as a parameter.

Upvotes: 0

Nick
Nick

Reputation: 147146

Using your simplified example, you could achieve what you want with this query, which checks that the number of menu codes for a price (COUNT(m.code)) is 2 and that they are 10001 and 10002:

SELECT p.price_id, p.site
FROM price p
JOIN price_menu pm ON pm.price_id = p.price_id
JOIN menu m ON m.menu_id = pm.menu_id
GROUP BY p.price_id, p.site
HAVING SUM(CASE WHEN m.code IN (10001, 10002) THEN 1 END) = COUNT(m.code) 
   AND COUNT(m.code) = 2

Output:

price_id    site
1002        151

Demo on dbfiddle

Upvotes: 1

Related Questions