CODEWITHSUNDEEP
mysqlsql
John Miller
John Miller

Reputation: 717

Select where either column value but not both

There's a table T0 that looks like this and using MySQL 5.7. Am storing ids in id, text in C1, and NULL or integers in C2 - NULL being the default value. An entry in C1 can have multiple values in C2. If you know about Closure tables, you'll notice that this table is slightly different in that the C1 entries do not correspond to id entries.

id | C1 | C2
---+----+-----
1  | a  | NULL
2  | b  | NULL
3  | c  | NULL
4  | c  | 3
5  | c  | 5
6  | d  | NULL

C1's value is stored in C2. The table is set such that C1 cannot have duplicate values in C2. So c, for example, cannot have two 3s. So the table is unique that way.

Would like to have a query return all rows where field C1 value is 3 and if C1 value is not 3, return row where C1 value is NULL.

Have tried the following code but to no avail.

SELECT *
FROM T0
WHERE C2 = 3 OR C2 IS NULL

So the query should be able to return 1, 2, 4 and 6. The desired result is:

id | C1 | C2
---+----+-----
1  | a  | NULL
2  | b  | NULL
4  | c  | 3
6  | d  | NULL

Upvotes: 1

Views: 2181

Answers (4)

Rajat
Rajat

Reputation: 5803

How about using a UNION?

SELECT * FROM t0 where C2=3 
UNION 
SELECT * FROM t0 where C2 IS NULL AND C1 NOT IN (SELECT C1 FROM t0 WHERE C2<>3) ORDER BY id;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Do you have to display the ID? If not, then this gets easy-peasy:

select c1, max(c2)
from t0
where c2 = 3 or c2 is null
group by c1
order by c1;

Upvotes: 1

derek.wolfe
derek.wolfe

Reputation: 1116

This is a simpler approach and would be faster than doing a join when there are a lot of rows in the table. 

SELECT * 
  FROM T0 
 WHERE (C2 = 3) 
    OR (C2 IS NULL AND 
        C1 NOT IN (SELECT C1 FROM T0 WHERE C2 = 3))

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272106

You can check that condition using NOT EXISTS:

SELECT *
FROM t
WHERE (
    C2 = 3
) OR (
    C2 IS NULL AND NOT EXISTS (
        SELECT 1
        FROM t AS x
        WHERE x.C1 = t.C1 AND x.C2 = 3
    )
)

Upvotes: 2

Related Questions