Correlated Subquery problem

[EONTECH]

Sample Data (assume table is called Chases):

ItemID CollectorID CollectorStatus
101 205 1
102 205 2
103 208 2
104 208 3
105 208 2
106 209 1
107 209 2
108 210 2

I would like to write a single query that will give the ItemIDs and the CollectorID for ONLY those Collections (a Collection is all records with the same CollectorID) where ALL items have at least a status of 2.

Appreciate any help on this!

 

[ablackwe]

Try the following

select c.ItemID, c.CollectorID, c.CollectorStatus
from Chases c
where c.CollectorID in (Select c1.CollectorID from Chases c1 where c1.CollectorStatus = 2)

Hope this helps
Thanks

Adam Blackwell
Information Analyst

http://www.workinglinks.co.uk/

 

[bborissov]

select Chases.ItemID,
       Chases.CollectorID,
       Chases.CollectorStatus
from Chases
INNER JOIN (SELECT CollectorID,
                   MAX(CollectorStatus) AS CollectorStatus
            FROM Chases
            GROUP BY CollectorID
            HAVING MAX(CollectorStatus) = 2) Tbl1
ON Chases Chases.CollectorID = Tbl1.CollectorID

That will filter these collections which have ALL records with status 2, if you want to include other records (these with status 3), change

HAVING MAX(CollectorStatus) = 2

to

HAVING MAX(CollectorStatus) > 1

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[EONTECH]

Thanks for the suggestios, but they both still include CollectorIDs that DON'T have a status greater than 1. For example, using the data I provided in the original post, the result set should be:

ItemID CollectorID
103 208
104 208
105 208
108 210

Only CollectorIDs 208 and 210 have ALL their Items with a status greater than 1. No items from CollectorID 209, for example, should be returned because 209 has at least one item with a status of 1.

Thanks

 

[bborissov]

Sorry, It should be MIN not MAX()

DECLARE @Test TABLE (ItemID Int, CollectorID Int, CollectorStatus int)
INSERT INTO @Test VALUES(101,205,1)
INSERT INTO @Test VALUES(102,205,2)
INSERT INTO @Test VALUES(103,208,2)
INSERT INTO @Test VALUES(104,208,3)
INSERT INTO @Test VALUES(105,208,2)
INSERT INTO @Test VALUES(106,209,1)
INSERT INTO @Test VALUES(107,209,2)
INSERT INTO @Test VALUES(108,210,2)

select Chases.ItemID,
       Chases.CollectorID,
       Chases.CollectorStatus
from @Test Chases
INNER JOIN (SELECT CollectorID,
                   MIN(CollectorStatus) AS CollectorStatus
            FROM @Test
            GROUP BY CollectorID
            HAVING MIN(CollectorStatus) = 2) Tbl1
ON Chases.CollectorID = Tbl1.CollectorID

Result:

103	208	2
104	208	3
105	208	2
108	210	2

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

here you go

create table blah (ItemID int,  CollectorID int , CollectorStatus int)


insert blah values(101 ,    205,            1)
insert blah values(102 ,    205,            2)
insert blah values(103 ,    208,            2)
insert blah values(104 ,    208,            3)
insert blah values(105 ,    208,            2)
insert blah values(106 ,    209,            1)
insert blah values(107 ,    209,            2)
insert blah values(108 ,    210,           2)


select b1.* from blah b1 left join (
select CollectorID from blah where collectorstatus <2 )b2
on b1.CollectorID = b2.CollectorID
where b2.CollectorID is null

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[EONTECH]

Both of these suggestions work well. I will see which one is more effecient with the actual data. Thanks, everyone!

Kevin (EONTECH)
EasyOffice Network