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.