query to find engineering student

srinandan

I am having problem to build a query. I have following data in a table. NAME SUBJECT ------------------ sri physics sri chemistry sri math amit physics amit bio amit math sanjay Phsysics sanjay chemistry sanjay math Ritu physics Ritu Chemistry Ritu bio i need to find the name of student (engineering) who are studying physics, Chemistry and math. in above table output should be as follows: NAME -------- sri sanjay Can anyone tell the sql query. Thanks, Sri...

Niladri_Biswas

Try this

declare @t table(name varchar(50),subject varchar(50))
insert into @t
select 'sri','physics' union all
select 'sri','chemistry' union all
select 'sri','maths' union all
select 'amit','physics' union all
select 'amit','bio' union all
select 'amit','maths' union all
select 'sanjay','physics' union all
select 'sanjay','chemistry' union all
select 'sanjay','maths' union all
select 'Ritu','physics' union all
select 'Ritu','chemistry' union all
select 'Ritu','bio'

select name from @t
where subject ='maths' or subject ='physics' or subject ='chemistry'
group by name
having (COUNT(name)>2)

Output:

name

sanjay
sri

:)

Niladri Biswas

srinandan

Thanks Biswas, Its working fine Thanks, Sri...