CODEWITHSUNDEEP
sqlsql-server
G8371
G8371

Reputation: 63

SQL Left-Join - Get value If both values are Not null in TableB or Row missing in TableB

http://sqlfiddle.com/#!18/d1cf0/6

TABLE HEADER 
(
     [HeaderID] VARCHAR(3), 
     [description] VARCHAR(50),
      [ProcessStatus] bit 
);

TABLE DETAILS
(
    [HeaderID] VARCHAR(3), 
    [DETAIL_VALUE_1] VARCHAR(50),
    [DETAIL_VALUE_2] VARCHAR(50)
);

SELECT DISTINCT 
    H.HEADERID 
FROM 
    HEADER H 
LEFT JOIN
    DETAILS D ON H.HeaderID = D.HeaderID 
              AND (D.DETAIL_VALUE_1 IS NOT NULL AND 
                   D.DETAIL_VALUE_2 IS NOT NULL)
WHERE H.ProcessStatus = 0

Based on the sample data provided in SQL Fiddle, the above query returns 1,2,3..

I need the query to return only 2,3 as the header 1 has one of the records where DETAIL_VALUE_1 is not null but DETAIL_VALUE_2 is null.

These tables might have million+ records. Header table is the anchor..i.e the Header table will have a flag 'ProcessStatus' to indicate which records have already been processed.

Thanks in advance.

Upvotes: 6

Views: 81

Answers (3)

Nick
Nick

Reputation: 147146

You can do this by utilising the fact that COUNT only counts non-null values, and checking that the count of HeaderID values in DETAILS is the same as the count of DETAIL_VALUE_1 and DETAIL_VALUE_2 values. This query also works where there are no rows in DETAILS because all of the COUNT values are 0.

SELECT H.HeaderID
FROM HEADER H 
LEFT JOIN DETAILS D ON H.HeaderID= D.HeaderID
GROUP BY H.HeaderID
HAVING COUNT(D.HeaderId) = COUNT(D.DETAIL_VALUE_1) AND COUNT(D.HeaderID) = COUNT(D.DETAIL_VALUE_2)

Output:

HEADERID
2
3

SQLFiddle

Upvotes: 1

Clay
Clay

Reputation: 5084

It sounds to me like you want to find rows where the "bad condition" doesn't exist...kinda double negative, perhaps?

select 
  H.HEADERID 
from
  HEADER H 
where 
  not exists 
  ( 
    select * 
    from 
      Details D 
    where
      H.HeaderID= D.HeaderID 
      and 
      (
        D.DETAIL_VALUE_1 IS NULL 
        or
        D.DETAIL_VALUE_2 IS NULL
      )
    )

Upvotes: 2

S3S
S3S

Reputation: 25112

You can use NOT IN

SELECT H.HEADERID 
FROM HEADER H 
WHERE HeaderID not in (select d.HeaderID from DETAILS d where (DETAIL_VALUE_1 is null or DETAIL_VALUE_2 is null))

Notice I didn't JOIN to the table, and I removed the DISTINCT since neither were needed.

http://sqlfiddle.com/#!18/d1cf0/15/0

If you plan to use columns from DETAILS you can still join to it.

SELECT DISTINCT H.HEADERID 
FROM HEADER H 
LEFT JOIN DETAILS D on
D.HeaderID = H.HeaderID
WHERE H.HeaderID not in (select d.HeaderID from DETAILS d where (DETAIL_VALUE_1 is null or DETAIL_VALUE_2 is null))

Upvotes: 1

Related Questions