CODEWITHSUNDEEP
mysql
The F
The F

Reputation: 3714

Select by multiple values in left join

Working with following structure, how can I select a subset of requests respecting multiple conditions in this 1:n relation?

# Table 1: Request
uid
name

# Table 2: Additional Information
uid
type
value
request

Note for table 2: type can be anything, i.e. 'product_name' or 'rating'.

If I'd just want to select Requests by a given product_name I can do this:

SELECT * FROM request as r 
    LEFT JOIN additional_information as i 
    ON r.uid = i.request 
    WHERE i.type = 'product' AND i.value = 'Product Name'

I'm stuck at what my statement must look like if I want to select Requests by a given product_name AND rating. I have tried to simply add another join but this gave me all requests that related to a given product_name as well as all requests related to a given rating. I need the statement to respect both conditions.

This, as mentioned, does not work for me.

SELECT * FROM request as r 
    LEFT JOIN additional_information as i 
    ON r.uid = i.request
    LEFT JOIN additional_information as a 
    ON r.uid = a.request 
    WHERE i.type = 'product' AND i.value = 'Product Name' 
    OR a.type = 'rating' AND a.value = 1

Appreciate the help!

Upvotes: 0

Views: 1299

Answers (2)

DRapp
DRapp

Reputation: 48139

It might be slow from the other offering since it is joining to your additional data TWICE, once for product, again on rating. This should probably be changed to a single left-join otherwise you could get a Cartesian result bloating your answer.

SELECT * 
   FROM 
      request as r 
         LEFT JOIN additional_information as i 
            ON r.uid = i.request
           AND (   ( i.type = 'product' AND i.value = 'Product Name' )
                OR ( i.type = 'rating' AND i.value = 1 );

Upvotes: 0

Rahul
Rahul

Reputation: 77866

Move those conditions from WHERE to JOIN ON condition like

SELECT * FROM request as r 
    LEFT JOIN additional_information as i 
    ON r.uid = i.request
    AND i.type = 'product' AND i.value = 'Product Name'    
    LEFT JOIN additional_information as a 
    ON r.uid = a.request 
    AND a.type = 'rating' AND a.value = 1;

And Yes absolutely, considering the valuable comment (which missed) from Strwabery, instead of doing a select * or select r.* you might actually want to specify the column names you want to fetch which is better than * performance wise since you are not getting unnecessary data using projection; unless you really want to fetch everything.

Upvotes: 1

Related Questions