Big ass query?

Anon

Hi ya'll!

I have a little problem with a query, no actually not o little problem but a big one. I haven't got o clue how to write it.

This is what I want:
This is the table

id | drink_id | ing_id | vol_id

1 | 10 | 1 | 5
2 | 10 | 5 | 1
3 | 10 | 2 | 3
4 | 11 | 5 | 6
5 | 11 | 3 | 23
5 | 11 | 9 | 57
6 | 12 | 34 | 11
7 | 12 | 21 | 9
8 | 12 | 32 | 34
9 | 13 | 89 | 3
10 | 13 | 76 | 7
11 | 13 | 23 | 54
12 | 13 | 7 | 10
13 | 14 | 5 | 22
14 | 14 | 1 | 34
15 | 15 | 39 | 21
16 | 15 | 27 | 2
17 | 15 | 50 | 5
18 | 16 | 3 | 45
19 | 16 | 5 | 35

If a person enters that he has the following
ing_id 1, 2, 3, 5
I whant to get the drink_id wich has 1, 2, 3 OR 5 but NOTHING else. So if I would run this query I would get the following drink_id:
10 because it has ing_id 1,5,2
14 because it has ing_id 5,1
16 because it has ing_id 3,5

NOT
11 because it has ing_id 5,3 AND 9 and there is not a 9 in the list.

Someone?

Anon

You can do it like this:

select

*
from
tblDrink
where
ing_id LIKE '[1235]'
and drink_id not in (
select
drink_id
from
tblDrink
where
ing_id NOT LIKE '[1235]')

I wrote this query and ran it on MS SQL. If the database you are using doesn't support some of the constructs I've used (e.g. NOT EXISTS and LIKE), I'm sure you could quite easily modify it to suit.

Cheers,
Brendan.

[deleted]

Tricky one.
Here's a solution in MySQL:

select drink_id,
count(*) as c,
sum(if(a.ing_id in (1,2,3,5),1,0)) as d
from a
group by a.drink_id
having c=d

This will group all results by drink_id, so everything that happens next is done on the group of records that share the same drink_id

count(*) as c
will count how many records there are for each drink_id

sum(if(a.ing_id in (1,2,3,5),1,0)) as d
will count how many records there are where ing_id has a value out of (1,2,3,5)

having c=d
tells mysql to only return results if c=d, or the total count of records is the same as the count of records where ing_id is in (1,2,3,5)

Anon

Thanx man! I thick it works. You are my new hereo ;o)

Anon

Thanx man! but I think the other reply was better. MySQL does not suport sub-selects ;o)