[RESOLVED] SELECT ... WHERE item = 1 AND item = 2 ... AND item = 12

[henan]

Hi all!
My brain has stopped working and need some input for a query build from two tables.

tbl1

id | type | ... more fields

1 | 1 |
2 | 1 |
3 | 1 |

4 | 2 |

tbl2

relid | item

1 | 1 |
1 | 2 |
1 | 5 |

1 | 8 |

2 | 1 |
2 | 2 |
2 | 5 |

2 | 9 |

3 | 3 |
3 | 4 |

3 | 5 |

4 | 1 |

4 | 2 |

where relid is related to id in tbl1.

Now I have some values (1,2 and 5) and I like to select all [id] from tbl1 where [id] = [relid] AND item = 1 AND item = 2 AND item = 3.

There can be as many as 30 items and as low as none.

Hope someone can follow my twisted mind ;-)

[Lars_Berg]

select id, type, ...
  from tbl1
 where id in
  (select relid 
      from tbl2
    where item in (1,2,3) 
    group by relid
    having count(distint item) = 3)

[henan]

Perfect - works like a charm! Thanks