9

Let's say I have a table making a junction between two tables... like this:

id_product | id_category
-----------------
11 | 22
11 | 33
12 | 22
12 | 33

I want to get id_products (distinct) according to a list of searched categories IDs.

If I use the IN() clause, the list of id_categories uses a logical OR.

How can I make a SELECT query to have logical ANDs for the list of id_categ submitted??

Example: I want all the id_products belonging to category 22 AND 33 (and possibly 5+ more Categ. IDs)

I found this answer: Using MySQL IN clause as all inclusive (AND instead of OR) ...but the query is mixing more than 1 table... I only want a query on a single table, the junction one.

Community
  • 1
  • 1
ecchymose
  • 663
  • 6
  • 19
  • How can a category be equal to 22 AND 33 at the same time? – Kermit Aug 15 '12 at 20:16
  • I asked the exact same question a while ago: http://stackoverflow.com/questions/11157772/sql-query-to-find-products-matching-a-set-of-categories – Tchoupi Aug 15 '12 at 20:18

2 Answers2

12

reading your link, I think it would be something like

select id_product 
from yourTable
where id_category in (--your List goes Here)
group by id_product 
having count(distinct id_category) = NumberOfElementsOfYourList

you should use = if only wants to get that id_category, but no others id_category. If not, use >=

Arth
  • 12,789
  • 5
  • 37
  • 69
Gonzalo.-
  • 12,512
  • 5
  • 50
  • 82
  • 1
    I changed `COUNT(distinct id_product)` to `COUNT(distinct id_category)` in your `HAVING` clause... `COUNT(distinct id_product)` will always be 1 for each id_product group. – Arth Aug 14 '14 at 17:01
  • you just saved my day, today. :-) – Krenor Feb 29 '16 at 09:48
1
select id_product
from your_table
where id_category in (22, 33)
group by id_product
having count(distinct id_category) = 2

You can add a having clause that counts the found id_category's. If you look for 5 IDs for instance, you have to change the 2 in 5 in the having clause.

juergen d
  • 201,996
  • 37
  • 293
  • 362