CODEWITHSUNDEEP
mysqlsqlruby-on-railsjoin
maulik bafalipara
maulik bafalipara

Reputation: 248

Multiple and condition with joined table

I am trying to find a record in a single MySQL query but I can't please have a look and help me if you know anything.

products(Table 1)
id  | title
----------
1   | Test 1
2   | Test 2
3   | Test 3

nutritions (Table 2)
id | product_id | title    | evaluation(string)
-------------------------------
1  | 1          | calories | 8
2  | 3          | proteins | 5
3  | 3          | calories | 8
4  | 3          | Sugar    | 9
5  | 2          | calories | 8
6  | 2          | Sugar    | 9

I want to apply a filter on the products table as below.

case 1:

(nutritions.title = 'calories' AND nutritions.evaluation = '8') and (nutritions.title = 'Sugar' AND nutritions.evaluation = '9')

case 2:

(nutritions.title = 'calories' AND nutritions.evaluation = '8') and (nutritions.title = 'Sugar' AND nutritions.evaluation = '9') and (nutritions.title = 'proteins' AND nutritions.evaluation = '5')

expected output:

products(case 1)                       products(case 2)
id  | title                            id  | title
----------                            --------------
2   | Test 2                           3   | Test 3
3   | Test 3

I tried the below query but it takes time I have more than 70k products.

Product.find_by_sql("
        select `products`.* from products 
            JOIN nutritions where products.id = nutritions.product_id 
                and (nutritions.title = 'calories' and nutritions.evaluation = '8') 
                and nutritions.product_id IN(
                        select product_id 
                        from nutritions 
                        where nutritions.title = 'Sugar' 
                        and nutritions.evaluation = '9'
                        )
            ")

Upvotes: 0

Views: 45

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

There are several ways to approach this. One method is to use exists:

select p.*
from products p
where exists (select 1
              from nutritions n
              where n.product_id = p.id and
                    n.title = 'calories' and
                    n.evaluation = '8'
             ) and
      exists (select 1
              from nutritions n
              where n.product_id = p.id and
                    n.title = 'Sugar' and
                    n.evaluation = '9'
             ) ;

With an index on nutritions(product_id, title, evaluation), this should have very good performance.

Upvotes: 2

RiggsFolly
RiggsFolly

Reputation: 94662

Basically a column cannot be 2 or more things at the same time, by that I meam nutritions.title cannot be calories and Sugar

So instead of using AND use OR

    (nutritions.title = 'calories' AND nutritions.evaluation = '8') 
OR  (nutritions.title = 'Sugar' AND nutritions.evaluation = '9')

Upvotes: 0

Related Questions