SQL question / advice

[eluser]nevsie[/eluser]
Right, sorry, said I would be back!!!
I have everything working and in principle it does as you suggested, however the IN part of the query does not perform as i wished (and most likely i did not explain correctly above)...

As far as i have read, IN will pull back all rows which have ANY of those values in it... (effectively and OR on each of those values). Therefore using my example from above:

Code:

prod_id     cat_id
  1         123
  2         123
  3         123
  4         123
  1         125
  2         126
  3         126
  5         127

cat_ids “123” and “125” using an IN would return "1", "2", "3", "4"
cat_ids “123” and “127” using an IN would return "1", "2", "3", "4", "5"

Where as i really wanted an AND where which only returns a distinct prod_id if both cat_ids are met...
cat_ids “123” and “125” wanted results "1"
cat_ids “123” and “127” wanted results "no results"

Does this make more sense? will have a look and play around with the subquery as i am only ever going to have two Cat_ids in this current instance...
Hope you can offer a magic solution, i am getting tired eyes now!!! And you help is truly appreciated! N