Trying to return a MySQL when the value is one of 2 OR and an AND

Question

Ok I am having some trouble with this one, for some reason this is not working.

I am trying to get a list of trades made by a user or to the user that are still active.

SELECT * FROM trades WHERE active = 1 and trader = 1 or tradee = 1 ORDER BY trade_date ASC

This returns non active trades because the tradee or trader equals 1. I have even tried other variations.

SELECT * FROM trades WHERE trader = 1 OR tradee = 1 AND active = 1 ORDER BY trade_date ASC

Any help would be appreciated.

Understanding operator precedence is really one of the most elementary programming skills. See http://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or — Bill Karwin, Mar 12 '14 at 16:21

score 0 · Answer 1 · answered Mar 12 '14 at 16:20

0

If I understand correctly, you want parenthesis:

SELECT * 
FROM trades 
WHERE 
    (trader = 1 OR tradee = 1)
    AND active = 1 
ORDER BY trade_date ASC

answered Mar 12 '14 at 16:20

planestepper

3,277
26
38

You could use `XOR` for that. Your version needs some extra parentheses also, btw. – Bart Friederichs Mar 12 '14 at 16:23
What extra parenthesis? – planestepper Mar 12 '14 at 17:12

score 0 · Answer 2 · answered Mar 12 '14 at 16:22

0

Use parentheses (and read up on boolean algebra):

SELECT * FROM trades WHERE active = 1 and (trader = 1 or tradee = 1) ORDER BY trade_date ASC

answered Mar 12 '14 at 16:22

Bart Friederichs

33,050
15
95
195

score 0 · Answer 3 · answered Mar 12 '14 at 16:22

0

Place your two OR conditions inside of parentheses:

SELECT
         *
FROM
         trades
WHERE
         active = 1
AND
         (
                  trader = 1
         OR       tradee = 1
         )
ORDER BY
         trade_date ASC

answered Mar 12 '14 at 16:22

David H. Bennett

1,822
13
16

Trying to return a MySQL when the value is one of 2 OR and an AND

3 Answers3