0

Hopefully someone can help me out here.

I have a table structure like this 

product_id  filter_id

374         54
374         55
37          64
375         52
375         55
375         56
375         64

I have a list of product_id's that I am using to do a WHERE IN 

select product_id from product_filter WHERE product_id IN (375,37,251,252,261,262,263,264,269,270,271,272,277) and filter_id = 55 and filter_id = 56

I am trying to return all the prodcut ids that match the two filter AND statements. I run this and get nothing back, I also tried doing an INNER join back on the same table, but again nothing returned. Any idea how I can do this?

My expected result would be product 375 returned since it matches both "AND" filter_id statements.

Any help would be appreciated.

Arun Palanisamy
  • 5,281
  • 6
  • 28
  • 53
limit
  • 647
  • 2
  • 8
  • 27

4 Answers4

2

You cannot have a condition where filter_id = 55 and filter_id = 56 because it's impossible for a single row to have a filter_id of 55 AND a filter_id of 56.

One option here:

select product_id from product_filter WHERE product_id IN (375,37,251,252,261,262,263,264,269,270,271,272,277) AND filter_id IN (55, 56)
Eden Dowling-Mitchell
  • 271
  • 1
  • 5
  • 1
    Good question and maybe bad wording on my part! I find this to be the best option of the two immediate options I thought of, which would be using OR on filter_id vs using IN on filter_id. The performance of IN is usually considered to be better than the performance of OR (see https://stackoverflow.com/questions/782915/mysql-or-vs-in-performance). But honesty, I just meant that of all the possible ways to get the results the OP wants, the option I suggested is simple, easy to read and given the lack of context around indexes or table size, should be efficient :) – Eden Dowling-Mitchell Feb 26 '20 at 09:53
  • 1
    Yes, my question was in fact about the wording :) – Cid Feb 26 '20 at 09:56
2

I think you are looking for this.

SELECT PRODUCT_ID FROM PRODUCT_FILTER
WHERE PRODUCT_ID IN (375,37,251,252,261,262,263,264,269,270,271,272,277)
      AND FILTER_ID IN (55,56) 
GROUP  BY PRODUCT_ID
HAVING COUNT(DISTINCT FILTER_ID) = 2;

CHECK DEMO HERE

You can group by the product_id and check the count of filter_id. This will ensure that product ID matches both values.

Arun Palanisamy
  • 5,281
  • 6
  • 28
  • 53
  • Thank you! This seems to work. Curious what is = 2 for ? – limit Feb 26 '20 at 10:12
  • 1
    `2` means we are checking for filter_id that matches 2 values _(55 & 56) in your case_ . If you want to check for 3 values say 55,56 & 57, then you have check for `COUNT(DISTINCT FILTER_ID) = 3` @limit – Arun Palanisamy Feb 26 '20 at 10:16
1

Your options for the 2nd Filter filter_id must be group by () and each option added withor`:

select product_id from product_filter WHERE product_id IN (375,37,251,252,261,262,263,264,269,270,271,272,277) and (filter_id = 55 OR filter_id = 56)

Or you also use the IN function:

select product_id from product_filter WHERE product_id IN (375,37,251,252,261,262,263,264,269,270,271,272,277) and filter_id in (55, 56)
Lars
  • 1,426
  • 10
  • 25
  • Thanks. Yeah mySQL is my weak point. Using IN works, but still doesn't that match either 55 or 56? I need a specific match on if that product ID matches both values. – limit Feb 26 '20 at 09:47
  • It matches both values, see @ArunPalanisamy answer. – Lars Feb 26 '20 at 10:10
0

your code was wrong because it's impossible to have filter id both 55 and 56

try this:

select product_Id from product_filter
where product_id IN (375,37,251,252,261,262,263,264,269,270,271,272,277)
and filter_id in (55, 56)
18Man
  • 572
  • 5
  • 17