Search code examples
mysqldatabaseselectjoinrelational-division

Simple but Impossible single MYSQL One to Many Query


Staff Table

ID Name   Gender
1  John   Male
2  Adam   Male
3  Joella Female

Food Table

ID StaffID Name
1  1       Eggs
2  1       Bacon
3  1       Toast
4  2       Eggs
5  2       Bacon
6  3       Toast

I need the name of the MALE staff member who has consumed both eggs and toast.
The answer should be John, but everytime I used an AND clause it is zero results because it is looking at the same "row field". Using an OR returns the incorrect results.

I tried left join, standard join, and a few others.

SELECT field from table left join table2 on table.field=table2.field 
where field ='eggs' && field = 'toast' && gender = 'male'

The trick to this is I am trying to do this in a single query.


Solution

  • field cannot be eggs and toast at the same time, so join on the same table again

    SELECT field from table left join table2 ON table.field = table2.field
    left join table2 table22 ON table.field = table22.field
    WHERE table2.field = 'eggs' AND table22.field = 'toast' && gender = 'male'
    

    I'm also pretty sure that you don't want to join ON "field," but on some other column like the staffID.