[RESOLVED] AND OR SQL statement

[dcjones]

Hi all,

I have the sql statement below which works OK until I add the OR parts. When the OR parts are added I get duplicate rows in the output. I can not see what I am doing incorrectly.

This works

SELECT members.member_id, title, name_f, name_l, 
company_name, houseno, street, city, state, zip, country, added, 
payments.product_id, 
payments.completed 
FROM members 
JOIN 
payments 
WHERE 
members.member_id = payments.member_id 
AND members.added BETWEEN 2007-05-01 AND 2007-06-08 
AND payments.completed = 1 
AND payments.product_id = 20

This produces duplicate rows

SELECT members.member_id, title, name_f, name_l, 
company_name, houseno, street, city, state, zip, country, added, 
payments.product_id, 
payments.completed 
FROM members 
JOIN 
payments 
WHERE 
members.member_id = payments.member_id 
AND members.added BETWEEN 2007-05-01 AND 2007-06-08 
AND payments.completed = 1 
AND payments.product_id = 20
OR payments.product_id = 19
OR payments.product_id = 18

Can anyone seee why, I know it me doing it wrong.

[nemonoman]

If the results of any OR test is true, the row will be returned.

You probably want to enclose part of the query in parens

AND (payments.product_id = 20
OR payments.product_id = 19
OR payments.product_id = 18 )

or use the IN context

AND payments.product_id IN (18,19,20)

[dcjones]

Hi nemonoman

Working thanks to your help.

I used "AND payments.product_id IN (18,19,20)" which provides the correct results.

Many thanks