Need Query Help urgent - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need Query Help urgent

Author

Topic

suresh0534
Starting Member

3 Posts

Posted - 2012-01-31 : 20:29:56

Hi,
I have one table student, i.e stdnt_id,stdnt_name,stdnt_activity
i have values in student table like this.
stdnt_id stdnt_name stdnt_activity
1 kiran 5
1 kiran 10
1 kiran 15
2 sachin 5
2 sachin 10
3 venkat 5
3 venkat 10
3 venkat 15
4 kumar 5
4 kumar 10
5 naveen 5
5 naveen 10

Here stdnt_activity 5 mean -> Pending , 10 -> Inprogress, 15 means -> Closed

So, i want a query to dispaly the values of student table based on cndition.
the condition is for example:

The student kiran having stdnt_activity 5,10,15 where as sachin having stdnt_activity 5,10.

I need a query do diaplay all student records in a table which is having stdnt_activity 5,10 and not 15.

If student have stdnt_activity 5,10,15...we don't need to display to the user .

if student have stdnt_activity 5,10 then we need to display the values.

By above example i only need to diaplay the values of sachin,kumar, naveen (these student's doen's have 15).

while students kiran,venkat will have 5,10,15 ..so these records need to be ignored(no need to diaplay).

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-31 : 20:46:40

[code]
select s.stdnt_id
from students s
where s.stdnt_activity in ( 5, 10 )
and not exists
(
select *
from students x
where x.stdnt_id = s.stdnt_id
and x.stdnt_activity = 15
)
group by s.stdnt_id
having count(*) = 2
[/code]

KH
[spoiler]Time is always against us[/spoiler]

suresh0534
Starting Member

3 Posts

Posted - 2012-02-01 : 05:23:51

Thank you so much for you help...

quote:

Originally posted by khtan


select s.stdnt_id
from   students s
where  s.stdnt_activity in ( 5, 10 )
and    not exists
       (
           select *
           from   students x
           where  x.stdnt_id       = s.stdnt_id
           and    x.stdnt_activity = 15
       )
group by s.stdnt_id
having count(*) = 2

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources