Question 325097 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Select row from one table where multiple rows in another table may have determined values, the number of conditions varies

The situation is quite similar to that shown in this question Select row from one table where multiple rows in another table have determined values, but the condition is more complex.

I have two tables in MySQL:

Products:

  id  |  value
================
   1  |   foo
   2  |   bar   
   3  |   foobar
   4  |   barbar

And properties:

  product_id  |  property_id
=============================
       1      |       10
       1      |       11
       2      |       15     
       2      |       16
       3      |       10    
       3      |       11
       3      |       12
       4      |       10
       4      |       16
       5      |       16
       6      |       11

I may have a filter that defines various number of properties to be selected, let’s say I am interested in 3 of them. So I need to choose products and order them according to the following conditions:

  1. at first products having all 3 properties,
  2. then products having 2 properties only (if not matched by the first condition)
  3. then products having only 1 property (if not matched by conditions 1 and 2)

Within each condition the items are ordered according to property_id in ascending order.

So if I want to pick products with property_id = 10 or property_id = 11 or property_id = 12, then my results should be the following:

  1. product_id=3 (it has properties 10, 11 and 12)
  2. product_id=1 (it has properties 10 and 11)
  3. product_id=4 (it has property 10)
  4. product_id=6 (it has property 11)

How this can be efficiently done by MySQL?

Tags:

2

Answers


  1. 0

    Given that ordering the count result is not possible in the same query, we can lazily wrap it in another select as the from reference.

    SELECT *
FROM
    (SELECT p.id ,
           COUNT(DISTINCT property_id) as matches
    FROM   products as p 
           INNER JOIN properties as prop 
                   ON p.id = prop.product_id 
                      AND property_id IN (10, 11)
    GROUP BY p.id 
    HAVING Count(DISTINCT property_id) >= 1) as tbl
ORDER BY matches desc

    The query will return:

    id matches
    1 2
    3 2
    4 1

    Keep in mind product with id 6 won’t appear as it doesn’t exists in the products table.

    Fiddle: https://www.db-fiddle.com/f/g1iZeMRQo95KvVLcZrFedd/0

    It might be better for readability by using CTE, but I digress..

    Login or Signup to reply.
  2. 0

    Try this:

    select product_id,sum(case property_id when 10 then 1 when 11 then 1 when 12 then 1 else 0 end) as hits 
from  properties 
group by product_id
having hits >=1
order by hits desc
;

-- result set:
+------------+------+
| product_id | hits |
+------------+------+
|          3 |    3 |
|          1 |    2 |
|          4 |    1 |
|          6 |    1 |
+------------+------+

    Note: This is assuming the properties table does not have duplicate rows. If it does,we will need to use the derived table (select distinct * from properties) as tb instead of base table for the FROM clause.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search