Help with SQL query

Question

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 aditional 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.

I know the idea should be very simple, but I totally panicked when they gave me this assignment, because I keep trying.

Answer 1

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.

Answer 2

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

SQL has very powerful support for logical operators, you aren't limited to just AND.

Answer 3

You need and OR statement, try this:

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

Answer 4

If I'm understanding right and you want either ("AR") or ("PR" and SPECIAL = "1"), then try this:

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

Answer 5

I think all you need to do to get this to work the way you desire is to break up the different criteria into separate logical groupings surrounded by parenthesis. If I understand your question correctly, you want something like this:

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