CODEWITHSUNDEEP
phpmysql
user933791
user933791

Reputation: 381

mysql searching row's based on AND

Hi guy's I've been trying to figure this out for hours, but I can't seem to wrap my head around it..

Take a look at this sql table..

enter image description here

I'm trying to search for a product_id that exists in all filter_cat_id's, so in this example it would be product_id 30, because it exists with filter_cat_id 1 2 and 3.. Product_id 30 exists in the same filter_cat_id twice, otherwise I'd just count the product id's, then the distinct cat id's and make sure the count's match.

Any ideas? Is this possible in mysql? another option would be to use PHP.

Thanks!

Upvotes: 2

Views: 71

Answers (3)

user933791
user933791

Reputation: 381

SELECT
    product_id, count(i.product_id) as t
FROM
    (SELECT 
        *
    FROM
        tablename
    GROUP BY product_id , filter_cat_id) as i
GROUP BY product_id
HAVING t = 3

Upvotes: 1

vcardillo
vcardillo

Reputation: 1696

This should be smart enough to determine the number of distinct categories, and select a product_id that has that number of distinct categories.

SELECT product_id
FROM
    (SELECT DISTINCT product_id, filter_cat_id
    FROM test.play) a
GROUP BY product_id
HAVING count(product_id) = (SELECT count(DISTINCT filter_cat_id) FROM test.play)
;

Test Data:

enter image description here

Query Result:
30
40

Upvotes: 0

C3roe
C3roe

Reputation: 96250

I'm trying to search for a product_id that exists in all filter_cat_id's, so in this example it would be product_id 30, because it exists with filter_cat_id 1 2 and 3.

In general, this can be done by grouping by product_id and using HAVING COUNT(product_id) >= x – where x is the number of categories,

SELECT product_id FROM table GROUP BY product_id HAVING COUNT(product_id) >= 3

If you don’t know the number of categories upfront (so that you can insert the actual number into the query), you can get that value with a subquery, 

SELECT product_id FROM table GROUP BY product_id
  HAVING COUNT(product_id) >= (SELECT COUNT(DISTINCT category_id) FROM table)

Edit: OK, so apparently the category_ids to search for come from some kind of search form/filter mechanism.

In that case, the filtering of items has to be added – and the number to compare to must be embedded implicitly, but that should be no problem.

SELECT product_id FROM table
  WHERE category_id IN(1,2,3)
  GROUP BY product_id
  HAVING COUNT(product_id) >= 3

Upvotes: 1

Related Questions