CODEWITHSUNDEEP
sqlsql-serverconditional-statementswhere-clause
Marcelo Almeida Rocha
Marcelo Almeida Rocha

Reputation: 143

Two Condition Where-clause SQL

I need to filter some rows when 2 conditions are met, but not excluding the other rows.

Table:

idRow   idMaster    idList
1         10        45
2         10        46
3         10        47
4         11        10
5         11        98
6         14        56
7         16        28
8         20        55

Example:

When:

Expected result:

idRow   idMaster    idList
1         10        45
5         11        98
6         14        56
7         16        28
8         20        55

Running SQL Server 2014

I tried combinations of CASE IF but all cases only filter the idMaster=10,11 and idList=45,98, excluding the other rows

Upvotes: 0

Views: 320

Answers (2)

shawnt00
shawnt00

Reputation: 17915

You can indeed do this with a (nested) case. Hopefully this helps you understand better.

case idMaster
    when 10 then case idList when 45 then 1 end
    when 11 then case idList when 98 then 1 end
    else 1
end = 1

This might be the best though:

not (idList = 10 and idList <> 45 or idList = 11 and idList <> 98)

Overall it's usually beneficial to avoid repeating that list of values in multiple places. Both of these avoid the need to keep things in sync when changes come.

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

Although you didn't mentioned the database name, this following query logic will be applicable for all databases-

SELECT * 
FROM your_table
WHERE idMaster NOT IN (10,11)
OR (idMaster = 10 AND idList = 45)
OR (idMaster = 11 AND idList = 98)

Upvotes: 5

Related Questions