Select Where With Multiple of the same

I have a table called 'matches' where I associate a items in the table "numbers" with an item in the table "letters" via there id.

So it looks like

number_id, letter_id 
1,10 
2,10
3,10
5,11
4,23
7,19 
1,19
3,64

now the user inputs an array of numbers, say 1,2,3 and I have to find the letter where all of it's numbers are 1,2,3, which would be 10. If they gave 1,7 it would give them 19, if they gave 3 it would give them and 64.

How can I do that?

I've been tr开发者_StackOverflow社区ying to write a recursive function for this but it always breaks. is there some sort of:

SELECT letter_id WHERE **number_id***s* = 1,2,3. That would be perfect. :)

This may or may not work in all cases, but I tried with (1,2,3) and (1,7):

select distinct letter_id
from r r1
    inner join r r2 using (letter_id)
where r1.number_id in (1, 7)
    and r2.number_id in (1,7)
    and (r1.number_id  r2.number_id);

You'll have to be able to provide the (1,7) or (1,2,3) dynamically with some programming language.

Rocking baby at 3:30am...

EDIT: To complete the @Martin's answer, you can use order by field()

select letter_id 
from (
    select letter_id,
        group_concat(number_id order by field(number_id,2,1,3)) as numset
    from r 
    group by letter_id
    ) as Martin
where numset = '2,1,3';

If you can construct a string from your list of number_ids, you could use the following query:

select letter_id 
from (select letter_id, group_concat(number_id) as numset from `matches` 
      group by letter_id) as fred 
where numset = '1,2,3';

It is sensitive to order (eg. '2,1,3' would not match).

Since the previous comments made the problem more than what the OP was, here's another answer...

You may be able to work it out by having temporary tables:

create temporary table r_sum as
    select letter_id, count(*) as total
    from r 
    group by letter_id;
create temporary table r_count as
    select letter_id, count(*) as total
    from r
    where number_id in (1,2,3,7)
    group by letter_id;
select letter_id
from r_sum
    inner join r_count using (letter_id, total);

I think if this does not answer your question, I am not getting what you want and how to help you. If table is huge, you will have to create indexes on the temporary tables to help go faster. r is your original table in OP.

you have to use IN statement for that.

try below query for that.

SELECT letter_id WHERE number_id IN (1,2,3)

you can pass array variable into the IN statement if you have.

Thanks.