CODEWITHSUNDEEP
sqlsql-servert-sql
Roy
Roy

Reputation: 1225

Get the records which don't match the condition

I have three tables that have data like this:

Table CON_MEMBER:

CONTAINER_DATA_BO CONTAINER_NUMBER_BO
PARENT_REF_001 CHILD_REF_001
PARENT_REF_001 CHILD_REF_002

Table CON_DATA:

HANDLE CONTAINER_NUMBER
CHILD_REF_001 CHILD_001
CHILD_REF_002 CHILD_002

Table CON_CUSTOM_DATA:

CONTAINER_DATA_BO DATA_FIELD DATA_ATTR
CHILD_REF_001 QTY 10
CHILD_REF_001 CLASS CLASS 1
CHILD_REF_001 MAT MAT 1
CHILD_REF_001 TYPE BARREL
CHILD_REF_001 TRANSPORT 1
CHILD_REF_002 QTY 100
CHILD_REF_002 CLASS CLASS 2
CHILD_REF_002 MAT MAT 2
CHILD_REF_002 TYPE DRUM

Now I have written a query that will get all the child container numbers against the parent container for which the CON_CUSTOM_DATA.TRANSPORT is not 1

SELECT DISTINCT 
    cd.CONTAINER_NUMBER 
FROM 
    CON_MEMBER cm1 
INNER JOIN 
    CON_MEMBER cm2 ON cm1.CONTAINER_NUMBER_BO = cm2.CONTAINER_DATA_BO
INNER JOIN 
    CON_DATA cd ON cd.HANDLE = cm1.CONTAINER_NUMBER_BO
LEFT JOIN 
    CON_CUSTOM_DATA ccd ON cd.HANDLE = ccd.CONTAINER_DATA_BO
WHERE 
    cm1.CONTAINER_DATA_BO = 'PARENT_REF_001' 
    AND ccd.DATA_ATTR <> '1'

But the query returns both the child "CHILD_002" as well as "CHILD_001". What is am expecting from above query is to get the Child Container Number "CHILD_002", Since for this container TRANSPORT is not 1.

Please help me achieve this output.

Thanking you in advance.

Upvotes: 1

Views: 1160

Answers (1)

SteveC
SteveC

Reputation: 6015

You could try something like this

SELECT DISTINCT cd.CONTAINER_NUMBER 
FROM CON_MEMBER cm
     JOIN CON_DATA cd ON cm.CONTAINER_NUMBER_BO=cd.HANDLE
WHERE NOT EXISTS (SELECT 1
                  FROM CON_CUSTOM_DATA ccd
                  WHERE cd.HANDLE = ccd.CONTAINER_DATA_BO
                        AND ccd.DATA_FIELD='TRANSPORT'
                        AND ccd.DATA_ATTR=1);

Upvotes: 1

Related Questions