CODEWITHSUNDEEP
mysqlsqldatabase
CJ F
CJ F

Reputation: 835

XOR-like Select Statement

I'm having a bit of a brain cramp, and I'm not sure how to even word the question, so I'm going to do it by example...

I have a table, table_a, with some number of rows and I need to do a select based on 2 columns. Basically I want to select a row if A and B are true. However if B isn't true, I need to select the row where A is true and B is another value.

So given a table like this...

Col A    ColB
1        'X'
1        'Y'
2        'Z'

I want to say something like...

Give me all the rows where ColA='1' and ColB='X'. If that doesn't exist, give me the rows where ColA='1' and ColB='Y'. But I don't want (1,Y) if (1,X) exists.

Yikes. How can I do this efficiently?

Upvotes: 0

Views: 1089

Answers (4)

alan grobert
alan grobert

Reputation: 1

select T.colA,T.colB
from table T
where T.colA = ANY (select colA from table where colB != T.colB);

Upvotes: 0

fthiella
fthiella

Reputation: 49089

I'd like to use this query:

SELECT *
FROM yourtable
WHERE
  (ColA, ColB) = (SELECT ColA, ColB FROM yourtable WHERE (ColA, ColB)=(1,'X')
                  UNION ALL
                  SELECT ColA, ColB FROM yourtable WHERE (ColA, ColB)=(1,'Y')
                  ORDER BY (ColA, ColB)=(1,'X') DESC
                  LIMIT 1)

Subquery will return either (1, 'X') if there's at least one row that has ColA=1 and ColB='X', otherwise it will return (1,'Y') if there's at least one row with such values.

In case they both are present, ordering by (ColA, ColB)=(1,'X') DESC and using LIMIT 1 assures that the precedence is given to (1, 'X').

Or this one that uses OR:

SELECT *
FROM yourtable
WHERE
  ((ColA, ColB) = (1, 'Y')
   AND NOT EXISTS(SELECT NULL
                  FROM yourtable
                  WHERE (ColA, ColB)=(1,'X')))
  OR
  ((ColA, ColB) = (1, 'X'))

Please see fiddle here.

Upvotes: 1

sgeddes
sgeddes

Reputation: 62851

Here's one way to do it just using a LEFT JOIN if I understood your requirements correctly. 

SELECT T.ColA, T.ColB
FROM YourTable T
  LEFT JOIN (
    SELECT ColA
    FROM yourtable T
    WHERE ColB = 'X'
    ) T2 ON 
      T.ColA = T2.ColA
WHERE T.ColB = 'X' OR T2.ColA IS NULL

SQL Fiddle Demo

I was assuming if the same cola had multiple records, return the one with colb=X (if it exists). Otherwise, return the row as is -- hence 2 Z in the results:

Results:

COLA   COLB
1      X
2      Z

Upvotes: 0

Explosion Pills
Explosion Pills

Reputation: 191779

I would just use two separate queries to do this. The first query would be ColA = 1 AND colB = 'X'. If it does not return results, then the second query will run to acquire (1, Y).

Upvotes: 0

Related Questions