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

user_10317028 0
Viewed 76

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.

sql-server sql
3 Answers

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
      )
    )
user_14786540

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))
user_61678550

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

user_94737640
Related