Checking all linked records

Transact-SQL
1

Hi, apologies for what is a newbie question.

I have data that looks like this:

ID|SubID|Category
1|1|Fixed
1|2|Fixed
2|1|Unfixed
2|2|Unfixed
3|1|Fixed
4|1|Unfixed
5|1|Fixed
5|2|Unfixed

I need to know all the IDs where the Category is "Fixed" for all SubIDs (i.e. I'd want the query to return IDs 1 and 3).

How can I do this?

Thanks in advance!

3

create table Tab (ID int,SubID int,Category varchar(255))

insert into Tab (ID,SubID,Category)
values
(1,1,'Fixed'),
(1,2,'Fixed'),
(2,1,'Unfixed'),
(2,2,'Unfixed'),
(3,1,'Fixed'),
(4,1,'Unfixed'),
(5,1,'Fixed'),
(5,2,'Unfixed')

select * from tab where Category = 'Fixed'

4

That doesn't work. That would show me ID 5 as well - but I'm only interested in the IDs for which ALL SubIDs are "Fixed" (and for ID 5, one of the SubIDs is "Unfixed").

5

Possibly?

WITH cte AS (
SELECT ID, MAX(SubID) AS MAXID, SUM(CASE WHEN Category = 'Fixed' THEN 1 ELSE 0 END) AS CountFixed
FROM Tab 
GROUP BY ID
)
SELECT ID FROM cte WHERE MAXID = CountFixed;
6

select ID FROM Tab
pivot (
sum(subID) for category in (Fixed,Unfixed)
) pp
where unfixed is null

7 
SELECT ID
FROM tablename
GROUP BY ID
HAVING MIN(Category) = 'Fixed' AND MAX(Category) = 'Fixed'
--ORDER BY ID
8

It is too early in the morning, i am still waiting for the effect of coffee to kick in . . .

Doesn't that implied that the only Category for that ID is 'Fixed'

isn't the same as WHERE Category = 'Fixed'

9

No, not really. The difference is that WHERE would include IDs that had values in Category other than 'Fixed', such as 'Unfixed';. Your own code using WHERE demonstrated that, as aytri commented. The MIN/MAX technique makes sure that the ONLY value present is 'Fixed', which matches the requested requirement.

1 Like
10

Yes. you are absolutely right. (now that the coffee has kick in) I can see that now