CODEWITHSUNDEEP
sqlsql-server
Manju
Manju

Reputation: 2640

SQL query with 2 sub query condition

Master table:

MasterID | PartID  
1          1  
2          3  
3          5

Part table:

PartID | PartName | DeleteDate  
1        Part1      NULL  
2        Part2      NULL  
4        Part4      NULL  
5        Part5      08-04-2017

Result table:

MasterID | PartID  
2          3  
3          5

These two tables are present in two different database and i need to validate with a SQL query to fetch the record if Master table has PartID that are not in Part table and Master table has any PartID that are deleted in Part table (i.e. DeleteDate IS NOT NULL)

I'm trying with a below query, but not getting the expected results

Select  
    *  
FROM DBNAME.DBO.Master  
Where PartID in (  
    Select  
        PartID   
     from Part)   
and PartID not in (  
    Select   
        PartID   
    from Part   
    where DeleteDate is NOT NULL)

Please let me know how can i achieve this

Upvotes: 0

Views: 48

Answers (2)

Ali Yesilli
Ali Yesilli

Reputation: 2200

As an alternative way, you can use union for two cases

Select 
       t1.* 
From 
       DBNAME.DBO.Master t1,Part t2
Where 
       t1.PartID = t2.PartID(+) and t2.PartID is null
Union All
Select 
       t1.* 
From 
       DBNAME.DBO.Master t1,Part t2
Where 
       t1.PartID = t2.PartID and t2.DeleteDate is not null;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

One approach is to LEFT JOIN the Master table to the Part table and then retain any parts which either did not map to anything in the Part table, or mapped to a deleted part.

SELECT
    m.PartID,
    CASE WHEN p.partID IS NULL THEN 'missing' ELSE 'deleted' END AS status
FROM Master m
LEFT JOIN Part p
    ON m.PartID = p.PartID
WHERE
    p.PartID IS NULL OR         -- missing part
    p.DeleteDate IS NOT NULL    -- deleted (but present) part

Upvotes: 3

Related Questions