SQL query to fetch the result set with same status

user_1239341 0
Viewed 38

The table 1 is as follows,

ID FK1_ID FK2_ID
1 1 1
2 1 2
3 1 3

The table with FK2 is as follows,

ID Type Status
1 Type1 True
2 Type2 True
3 Type1 False

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?

sql-server sql
2 Answers

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
user_27447220

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
user_148689970
Related