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.
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.