CODEWITHSUNDEEP
sqlselect
Alfonso Fernandez-Ocampo
Alfonso Fernandez-Ocampo

Reputation: 887

SQL select when one condition or another are met but not both

Given the following table:

id      column1   column2
-------------------------
1       3         8
2       4         7
3       4         10
4       4         14
5       14        17
6       10        27
7       14        21
8       16        14
9       21        4
10      30        3

what is the nicest way to query selecting rows when number 4 and 14 are selected in column1 or column2 BUT exclude when number 4 and number 14 are both in the row. Be aware that the order can be reversed.

expected output

id      column1   column2
-------------------------
2       4         7
3       4         10
5       14        17
7       14        21
8       16        14
9       21        4

Upvotes: 9

Views: 26888

Answers (7)

Amol Nakhate
Amol Nakhate

Reputation: 1

select * from table where (column1=4 or column2=14) and not (column1=4 and column2=14);

Upvotes: 0

roman
roman

Reputation: 1

SELECT * FROM t WHERE column1 in(4,14) or column2 in(4,14) except SELECT * FROM t WHERE column1 in(4,14) and column2 in(4,14)

Upvotes: 0

roman
roman

Reputation: 1

with cte as(
    SELECT * FROM t WHERE column1 in(4,14) or column2 in(4,14)
)
SELECT * FROM t WHERE (column1 in(4,14) or column2 in(4,14)) and id not in(select id from cte where column1 in(4,14) and column2 in(4,14))

Upvotes: 0

Javlon Tulkinov
Javlon Tulkinov

Reputation: 598

SELECT * FROM table WHERE (column1=4 XOR column2=14) XOR (column1=14 XOR column2=4)

Upvotes: 13

Robby Cornelissen
Robby Cornelissen

Reputation: 97312

Don't know if it's the nicest way, but this should work:

SELECT * FROM t
WHERE (
    column1 IN (4,14)
        AND
    column2 NOT IN (4,14)
) OR (
    column1 NOT IN (4,14)
        AND
    column2 IN (4,14)
)

Upvotes: 1

SMA
SMA

Reputation: 37083

Try this:

SELECT *
FROM mytable
WHERE ((column1 = 4 AND column2 != 14)
OR    (column2 = 14 AND column1 != 4)
OR    (column1 = 14 AND column2 != 4)
OR    (column2 = 4 AND column1 != 14))

Upvotes: 2

Matt
Matt

Reputation: 15061

SELECT id, column1, column2
FROM table
WHERE column1 in ('4','14') AND column2 NOT IN ('4','14')
OR column2 in ('4','14') AND column1 NOT IN ('4','14')

Upvotes: 1

Related Questions