SELECT WHERE filter is not applied after using a LEFT JOIN-CodePudding

Say I have three tables transactions, items and users that looks something like this

#items

id  brand  price   transaction_id
-- ------ ------- ---------------
1  apple    10          1
2  apple    20          2
3  pear     15          1
4  banana   20          1

#transactions
id   user_id
-- ------ 
1    1    
2    1  
3    2
4    1

#users

id   system   value
--- --------- -------
1    gooogle   001
1    facebook  jashd28
2    google    002
2    facebook  jlak30

I then want to join those together such that I can see which (google) user has purchased what brands. Currently my query looks like

SELECT items.transaction_id,items.brand,users.value, items.price  FROM items
LEFT JOIN transactions 
    ON transactions.id= items.transactions_id
LEFT JOIN users
    ON users.id=transactions.user_id
    AND users.system='google'
WHERE items.transaction_id=1
AND LOWER(items.brand) LIKE '%apple%' OR LOWER(items.brand) LIKE '%pear%'

(the query above is not 100% the same as mine, but the structure is the same; multiple joins and then a WHERE clause at the end)

but it returns transaction_id other than 1. I can put that items.transaction_id into the JOIN but then it seems to return all items from transaction_id=1 and not the apple or pear

Expected:

id brand price user
-- ----- ----- -----
1  apple  10    001
3  pear   15    001

Outcome:

id brand price user
-- ----- ----- -----
1  apple  10    001
2  apple  20    002  #<- should not be there since it does not have transaction_id=1
3  pear   15    001
4  banana 20    001

CodePudding user response：

Your statement as shown is:

SELECT items.transaction_id,items.brand,users.value, items.price  FROM items
LEFT JOIN transactions 
    ON transactions.id= items.transactions_id
LEFT JOIN users
    ON users.id=transactions.user_id
    AND users.value='google'
WHERE items.transaction_id=1
AND LOWER(items.brand) LIKE '%apple%' OR LOWER(items.brand) LIKE '%pear%'

Your problem is that it is showing values that do not have items.transaction_id = 1

The reason is because you are mixing up AND and OR SQL qualifiers and because you are not correctly bracketing (with parentheses) them the ordering matters, so you're doing in effect:

WHERE (
       items.transaction_id=1
    AND 
       LOWER(items.brand) LIKE '%apple%'
     )
 OR LOWER(items.brand) LIKE '%pear%'

Your AND statement should use parentheses because you have an OR qualifier; so you need to construct the syntax logic of AND ( x OR z )

so:

WHERE items.transaction_id=1
    AND ( LOWER(items.brand) LIKE '%apple%' 
          OR LOWER(items.brand) LIKE '%pear%')

CodePudding user response：

as mentioned by Martin in above question one problem is the parenthesis are missing. Secondly you need to where condition apart from JOIN. So the final query would be

select distinct i.id, i.brand, i.price, u.value from items i
left join transactions t
on i.transaction_id = t.Id
left join users u 
on u.ID2 = t.user_id 
where i.transaction_id = 1 
and (i.brand like '%apple%' or i.brand like '%pear%')
and u.system = 'google'