Simple but Impossible single MYSQL One to Many Query

Question

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.

I think you should be using AND in place of && in your query. — Maximus2012, Oct 21 '13 at 21:00

score 3 · Accepted Answer · answered Oct 21 '13 at 21:01

3

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.

answered Oct 21 '13 at 21:01

Explosion Pills

188,624
52
326
405

@BillKarwin you're absolutely right; there is no need for a `LEFT JOIN` in this case (and it is nullified anyway) – Explosion Pills Oct 21 '13 at 21:34
Thanks! Worked like a charm. I never had to do a second join on the same table within a query. First time for everything! – Kauffju3 Oct 22 '13 at 18:03

score 2 · Answer 2 · answered Oct 21 '13 at 21:02

Group by staff member and then filter the resulting groups for those that satisfy the desired criteria:

SELECT   Staff.Name
FROM     Staff JOIN Food ON Food.StaffID = Staff.ID
WHERE    Food.Name IN ('Eggs', 'Toast')
     AND Staff.Gender = 'Male'
GROUP BY Staff.ID
HAVING   COUNT(Food.ID) = 2

score 2 · Answer 3 · answered Oct 21 '13 at 21:02

2

SELECT name, count(Food.id) AS foodcount
FROM Staff
LEFT JOIN Food ON Staff.id = Food.StaffID
WHERE Food.Name IN ('eggs', 'toast') AND gender = 'male'
GROUP BY Staff.id
HAVING foodcount = 2;

answered Oct 21 '13 at 21:02

Marc B

356,200
43
426
500

Don't you want count distinct in the event the eggs or toast are duplicated instead of one of each? – Taryn Oct 21 '13 at 21:03
OP never said if `food.id,food.staffid` is a unique tuple or not. if it isn't, then yeah, count(distinct) would make sense. – Marc B Oct 21 '13 at 21:05

score 1 · Answer 4 · answered Oct 21 '13 at 21:03

1

you can go with JOIN syntax, or with IN syntax

SELECT name from staf
where
ID in (select StaffId from food where Name='egg') and
ID in (select StaffId from food where Name='toast') and
gender = 'male'

answered Oct 21 '13 at 21:03

Iłya Bursov

23,342
4
33
57

score 0 · Answer 5 · answered Oct 21 '13 at 21:32

0

select s.name from staff_table s join food_table f1 on f1.staff_id=s.id join food_table f2 on f2.staff_id=s.id where f1.name='Toast' and f2.name='Eggs' and s.gender='Male'

answered Oct 21 '13 at 21:32

Pramod

29
3

Simple but Impossible single MYSQL One to Many Query

5 Answers5