CODEWITHSUNDEEP
sqloracle-databasegroup-by
Mikaelangel
Mikaelangel

Reputation: 181

How to fetch parent record based of existing children

I would like to retrieve parent records by a existing children.

For instance I have:

Parent Table : Meal

ID  Meal
1   A
2   B
3   C

Child Table: Ingredients

ID MealID  Ingredient
1  1         x
2  1         y
3  1         z
4  2         x
5  2         y
6  3         x

I want to retrieve all Meals that have Ingredients x and y. Query should return Meal A and B. I don't want to use any function like listagg or xmlagg to have all children names in a column and later use LIKE.

I really appreciate your help.

Thanks.

Upvotes: 0

Views: 72

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65373

You can use :

select m.meal
  from Meal m
  join Ingredients i on i.mealid = m.id
 where i.Ingredient in ('x','y') --
 group by m.meal                 --| => These two rows guarantees exactly returning of two rows with ingr. x & y
 having count(*)>1; ---------------

 MEAL
 -----
 A
 B

Edit(depending on your last comment):

You can retrieve all records from child where they have both ingredients X and Y by using exists as below :

 select i.ID
   from Ingredients i
  where exists ( select 1 
                   from Ingredients
                  where MealID = i.MealID
                    and i.Ingredient in ('x','y') 
                  group by MealID    
                 having count(*)>1 
                );

ID
--
1
2
4
5

Rextester Demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270683

You can use aggregation:

select mealid
from ingredients
where ingredient in ('x', 'y')
group by mealid
having count(*) = 2;

This assumes that ingredients are not repeated within a meal. If they can be, you want count(distinct ingredient) = 2.

Upvotes: 1

Related Questions