CODEWITHSUNDEEP
sqlsql-servert-sqlwhere-clause
mejiaks
mejiaks

Reputation: 15

Tricky WHERE condition clause

I got into a dilema that seems to be easy but i just can't put it in TSQL.

Let's say I have this Table

Service     Master
LOCAL       BRGIL
EREPO       CROMST
NICLI       CROMST
EXPORT      NIGIL

I need to disregard all records that in Master they have the value 'CROMST' unless the value in 'Service' is EREPO

so far i've tried

WHERE (Master <> 'CROMST' AND ([Service] <> 'EREPO'))

This only shows

Service     Master
LOCAL       BRGIL
EXPORT      NIGIL

i have tried this also

WHERE (Master <> 'CROMST' AND (rtrim([Service])+rtrim(Master) <> 'EREPOCROMST'))

and still no luck.

I know this should be easy but i am blocked

the result i am looking is 

Service     Master
LOCAL       BRGIL
EREPO       CROMST
EXPORT      NIGIL

Upvotes: 1

Views: 87

Answers (5)

CSY
CSY

Reputation: 208

select * from your_table a where not exists (select * from your_table b where b.Master = 'CROMST' and b.Service <> 'EREPO')

The subquery queries all rows where Master has the value 'CROMST' but the 'Service' value is not 'EREPO'.

The "not exists" filters out the subquery result.

Upvotes: 0

CPearson
CPearson

Reputation: 113

Using case function:

DECLARE @myTable AS TABLE
    (
        [SERVICE] VARCHAR(8) ,
        [Master] VARCHAR(8)
    );


INSERT INTO @myTable (   [SERVICE] ,
                         [Master]
                     )
VALUES ( 'LOCAL', 'BRGIL' ) ,
    ( 'EREPO', 'CROMST' ) ,
    ( 'NICLI', 'CROMST' ) ,
    ( 'EXPORT', 'NIGIL' );

    SELECT * FROM @myTable;

    SELECT *
    FROM @myTable
    WHERE 1 =   CASE WHEN ([MASTER] = 'CROMST') 
                    THEN
                        CASE WHEN ([SERVICE] = 'EREPO') 
                            THEN 1 -- special exception
                            ELSE 0 --disregard all others
                        END
                    ELSE 1 -- keep
                END

Upvotes: 1

JohnLBevan
JohnLBevan

Reputation: 24410

Use: 

where master <> 'CROMST' or Service = 'EREPO'

Evaluating the possible scenarios:

  • Scenario: master = 'CROMST'; Service = 'EREPO'
    • false or true
    • true
  • Scenario: master != 'CROMST'; Service = 'EREPO'
    • true or true
    • true
  • Scenario: master = 'CROMST'; Service != 'EREPO'
    • false or false
    • false
  • Scenario: master != 'CROMST'; Service != 'EREPO'
    • true or false
    • true

Upvotes: 2

Esteban P.
Esteban P.

Reputation: 2809

SELECT * FROM 
your_table
WHERE (Key2 != 'CROMST' OR Key1 = 'EREPO' )

would be my approach

Upvotes: 1

Degan
Degan

Reputation: 989

Try:

WHERE Master <> 'CROMST'
  OR (([Service] = 'EREPO') AND (Master = 'CROMST'))

Upvotes: 0

Related Questions