SQL | List all all tuples(a, b, c) if there exists another tuple with equal (b,c)

user_11337682 0
Viewed 195

I have three tables where the bold attribute(s) is the primary key

  • Restaurants(restaurant_ID, name, ...)
resturant_ID,  name,       ...
1,             Macdonalds   
2,             Hubert
3,             Dorsia
...           ...
  • Identifier(restaurant_ID, food_ID)
restaurant_ID, food_ID, ...
1,             1
1,             4
2,             1
2,             7
...            ...
  • Food(food_ID, name, ...)
food_ID    food_name
1          Chips
2          Burgers
3          Salmon
...        ...

Using postgres I want to list out all restaurants (restaurant_id and name - 1 row per restaurant) that have share the exact same set of foods with at least one other restaurant.

For example, let's say

  • Restaurant with ID "1" has only associated food_id's 1 and 4 as shown in Identifier
  • Restaurant with ID "3" has only associated food_id's 4 and 1 as shown in Identifier
  • Restaurant with ID "7" has only associated food_id's 6 as shown in Identifier
  • Restaurant with ID "9" has only associated food_id's 6 as shown in Identifier
  • Then output
Restaurant_id      name
1                  name1
3                  name3
7                  ...
9                  ...

Any help would be greatly appreciated!

Thank you

set postgresql sql equality
3 Answers

Use the aggregate function string_agg() to get the full list of foods for each restaurant:

with cte as (
  select restaurant_ID,
         string_agg(food_ID::varchar(10),',' order by food_ID) foods
  from identifier
  group by restaurant_ID
)
select r.* 
from Restaurants r inner join cte c
on c.restaurant_ID = r.restaurant_ID
where exists (select 1 from cte where restaurant_ID <> c.restaurant_ID and foods = c.foods)

But I would prefer to group restaurants based on matching foods:

with cte as (
  select restaurant_ID,
         string_agg(food_ID::varchar(10),',' order by food_ID) foods
  from identifier
  group by restaurant_ID
)
select string_agg(r.name, ',') restaurants
from Restaurants r inner join cte c
on c.restaurant_ID = r.restaurant_ID
group by foods
having count(*) > 1

See the demo.

user_104988280

Here is a way to get the unique set of resturants having exactly same food items. This uses array_agg() and array_to_string() functions

 With cte as
(select T.restaurant_id, array_to_string(array_agg(food_id), ',') as food_list
from
(select *
  from Identifier t1
  order by restaurant_id, food_id) T
  group by T.restaurant_id)

select 
   concat(r1.name,',',r2.name) as resturant_names,
   t1.restaurant_id as restaurant_id1,
   r1.name as restaurant_1,
   t2.restaurant_id as restaurant_id2,
   r2.name as restaurant_2,
   t1.food_list as common_food_ids
from cte t1
join cte t2
on t1.restaurant_id < t2.restaurant_id
and t1.food_list = t2.food_list
left join Restaurants r1
on t1.restaurant_id = r1.restaurant_id
left join Restaurants r2
on t2.restaurant_id = r2.restaurant_id;

EDIT : Here is a dB fiddle - https://dbfiddle.uk/?rdbms=postgres_12&fiddle=e2de05edfbe036cc0d81c64d60f0b599 . Also, just for reference, solution to the same problem in Oracle using listagg function - https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=12785c3d5abbca97be5d44dd45a6da4a

Update : Below query addresses the update output format of the question.

With cte as
(select T.restaurant_id, array_to_string(array_agg(food_id), ',') as food_list
from
(select *
  from Identifier t1
  order by restaurant_id, food_id) T
  group by T.restaurant_id)

select 
   --concat(r1.name,',',r2.name) as resturant_names,
   t1.restaurant_id as restaurant_id,
   r1.name as restaurant--,
  --t2.restaurant_id as restaurant_id2,
  --r2.name as restaurant_2,
  --t1.food_list as common_food_ids
from cte t1
join cte t2
on t1.restaurant_id = t2.restaurant_id
and t1.food_list = t2.food_list
left join Restaurants r1
on t1.restaurant_id = r1.restaurant_id
left join Restaurants r2
on t2.restaurant_id = r2.restaurant_id;
user_142366220

As I understand your question, you want all restaurants that have the same list of foods as restaurant 1.

If so, that's a relation division problem. Here is an approach using joins and aggregation:

select r.name
from identifier i1
inner join identifier i2 on i2.food_id = i1.food_id
inner join restaurant r on r.restaurant_id = i2.restaurant_id
where i1.restaurant_id = 1
group by r.restaurant_id
having count(*) = (select count(*) from identifier i3 where i3.restaurant_id = 1)
user_106767160
Related