SQL query to select where A=’foo’ and where (A=’bar’ and B=’baz’)

I’ve got the next SQL query:

SELECT FIRST_NAME, LAST_NAME
FROM MYTABLE
WHERE STATUS = 1 AND VARIABLE IN ( 'PR', 'AR' ) AND SPECIAL = 1;

SPECIAL is an additional condition which is added if I previously selected ‘PR’ (checkbox) in my form. The thing is, with this query, I’m no longer getting rows where VARIABLE is ‘AR’.

Now, the idea is that this query should return any rows when VARIABLE is ‘PR’ or ‘AR’, but if it is ‘PR’, from this ‘PR’-only group, it should return only the SPECIAL ones. I mean, it should still display rows with VARIABLE = ‘AR’, but if SPECIAL = 1, then the ones that are ‘PR’, should be only those where SPECIAL = 1.

Answer

Since it is an assignment I think it is better to explain how to do it than to just to give you the code to copy and paste.

You want to select a row where STATUS = 1 and one of:

• VARIABLE is ‘AR’

or:

• VARIABLE is ‘PR’ and SPECIAL = 1.

Use a combination of AND and OR. Remember that AND has higher precedence than OR. It is a good idea to use parentheses to be explicit about how the expression should be calculated.