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

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 con c.restaurant_ID = r.restaurant_IDwhere 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, ',') restaurantsfrom Restaurants r inner join cte con c.restaurant_ID = r.restaurant_IDgroup by foodshaving count(*) > 1

See the demo.

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_listfrom(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_idsfrom cte t1join cte t2on t1.restaurant_id < t2.restaurant_idand t1.food_list = t2.food_listleft join Restaurants r1on t1.restaurant_id = r1.restaurant_idleft join Restaurants r2on 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_listfrom(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_idsfrom cte t1join cte t2on t1.restaurant_id = t2.restaurant_idand t1.food_list = t2.food_listleft join Restaurants r1on t1.restaurant_id = r1.restaurant_idleft join Restaurants r2on t2.restaurant_id = r2.restaurant_id;

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.namefrom identifier i1inner join identifier i2 on i2.food_id = i1.food_idinner join restaurant r on r.restaurant_id = i2.restaurant_idwhere i1.restaurant_id = 1group by r.restaurant_idhaving count(*) = (select count(*) from identifier i3 where i3.restaurant_id = 1)