CODEWITHSUNDEEP
mysqljoin
user562854
user562854

Reputation:

MySQL left join multiple column pairs

Given the following table (products_filter):

enter image description here

How can I do a SELECT ... FROM products LEFT JOIN products_filter ... in such a way that it only returns products which have ALL the specified (filter_id,filter_value) pairs.

Example: for (filter_id, filter_value) = (1,1),(3,0) it should only return the product with id 90001, because it matches both values.

Upvotes: 0

Views: 766

Answers (2)

xQbert
xQbert

Reputation: 35323

As you only said you wanted the PRODUCTS values having the desired filter attributes... I've limited results to just product.*

The below query uses an inline view with the count of distinct filters by product ID. The outer where clause then uses the distinct count (in case duplicate filters could exist for a product) of the filter_IDs.

The # in the where clause should always match the number of where clause paired sets in the inline view.

Your sample data indicated that the paired sets could be a subset of all filters. so this ensures each filter pair (or more) exists for the desired product.

SELECT p.* 
FROM products p
LEFT JOIN (SELECT product_ID, count(Distinct filter_ID) cnt
           FROM products_Filter
           WHERE (Filter_ID = 1 and filter_value = 1)
              or (Filter_ID = 3 and filter_value = 0)
           GROUP BY Product_ID) pf
   on P.Product_ID = PF.Product_ID
 WHERE pf.cnt = 2

Upvotes: 0

sidharth.1989
sidharth.1989

Reputation: 15

If the specified filter pairs is restricted to a deifnite number the the following query should work.

Select a. Product_id
From products a
Left outer join
(Select product_id,filter_id,filter_value,count(*)
From product_filter
Where filter_id in (1,1) and filter_value in(3,0)
Group by product_id,filter_id,filter_value
Having count(*)=2)b
On(a.product_id=b.product_id)

Upvotes: 1

Related Questions