September 2, 2003 at 12:58 pm
I have a table which has 3 fields (studentid, specialcodeid, specialcodevalue). A student may have multiple specialcodeids. A sample data would be:
111 10 'abc'
111 11 '5'
111 12 '12/12/1980'
222 10 'xyz'
222 11 '6'
I would like to form a select statement that returns all studentids who have specialcodeid=10 and 11 and 12. i.e. student 111 above. Your help is appreciated.
--Ibrahim
September 2, 2003 at 2:13 pm
Possibly something here might work for you:
set nocount on
declare @test-2 table (studentid int, specialcodeid int, specialcodevalue varchar(100))
insert into @test-2 values (111 ,10 ,'abc')
insert into @test-2 values (111 ,11 ,'5')
insert into @test-2 values (111 ,12 ,'12/12/1980')
insert into @test-2 values (222 ,10 ,'xyz')
insert into @test-2 values (222 ,11 ,'6')
insert into @test-2 values (222 ,13 ,'6')
insert into @test-2 values (333 ,10 ,'abc')
insert into @test-2 values (333 ,11 ,'5')
insert into @test-2 values (333 ,12 ,'12/12/1980')
insert into @test-2 values (333 ,13 ,'12/12/1982')
-- This if you want the students that have all the possible specialcodeid's
select distinct t.studentid from @test-2 t
where (select count(distinct specialcodeid) from @test-2)
=
(select count(distinct specialcodeid) from @test-2
where studentid=t.studentid)
-- or this if some students have more than 3 specialcodeid's
SELECT studentid
FROM @test-2
WHERE specialcodeid IN (10,11,12)
GROUP BY studentid
HAVING COUNT(DISTINCT specialcodeid)=3
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 2, 2003 at 3:09 pm
Thanks Greg. I'll give that a try. I think the second solution may be the one.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply