The table 1 is as follows,

The table with FK2 is as follows,

The FK2_ID column is the ID column of table 2.

The expected result is, for any FK1_ID(which I have as a list of IDs), need to check all its FK2 entries in the 2nd table of Type1 and status True.

For example: Here, I want to return YES, if all the Type1 entries are True for the specific FK1_ID. Else NO.

So, for FK1_ID with 1, the FK2 table has 3 records. Of which Type1 has 2 records. I should return YES, if both Type1 records are True, else NO.

I want accomplish this using SQL. Any help is appreciated?

CodePudding user response：

I'm not totally following your logic (how these two tables are joined) but sounds like you want to compare a total count with a conditional count so maybe something like

with t as (select type, count(status) as cnt,
 sum(case when status ='True' then 1 else 0 end) as truecnt
 from FK2
group by type)
select type, case when truecnt > 0 and cnt = truecnt then 'Yes' else 'No' end as MyResult
from t

CodePudding user response：

Looks like you just need to compare a conditional count of Status to the full count, with a CASE for the final result.

SELECT
  t1.FK1_ID,
  Result = CASE WHEN COUNT(*) = COUNT(CASE WHEN FK2.Status = 'True' THEN 1 END)
    THEN 'Yes'
    ELSE 'No' END
FROM table1 t1
JOIN FK2 ON FK2.ID = t1.FK2_ID
  AND FK2.Type = 'Type1'
GROUP BY
  t1.FK1_ID;

A slightly shorter but less understandable version

CASE WHEN COUNT(*) = COUNT(NULLIF(FK2.Status, 'False'))

Alternatively

CASE WHEN COUNT(NULLIF(FK2.Status, 'True')) = 0