query issue - 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

General SQL Server Forums

New to SQL Server Programming

query issue

Author

Topic

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2013-11-11 : 08:04:24

hi everyone,

i am using table of student

stdid name subject year
1 arun chemistry 1996
2 alias maths 1996
3 babau history 1997
4 basha hindi 1998
5 hussain hindi 2001
6 chandru chemistry 2001
7 mani hindi 2002
8 rajesh history 2003
9 rama chemistry 2008
10 laxman maths 2008
11 naryan tamil 2010
12 das chemistry 2010

i want all records except chemistry and if chemistry records is there i want to avoid that year for all records also.
pls i need help

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 08:12:28

do you mean this?


select stdid, name, subject, [year]
from
(
select *,sum(case when subject='chemistry' then 1 else 0 end) over (partition by student,[year]) as cnt
from student
)t
where cnt=0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2013-11-11 : 23:28:55

thanks visakh murukes sir,

but i want , if chemistry like this ,

stdid name subject year
1 arun chemistry 1996

2 alias maths 1996

if chemistry year 1996, then all subject in 1996 also not to display.

pls...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-12 : 00:10:25

this ?


select *
from   student s
where  not exists
       (
           select *
           from   student x
           where  x.year = s.year
           and    x.subject = 'chemistry'
       )

if not, please post your expected result

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

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2013-11-12 : 01:42:10

thanks sir,

my query is if chemistry subject is year of 1996 then whatever recored on year of 1996 also not to display.result would be except subject chemistry and year 1996.
stdid name subject year
1 arun chemistry 1996

2 alias maths 1996

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-12 : 01:42:50

quote:
Originally posted by rajnidas

thanks visakh murukes sir,

but i want , if chemistry like this ,

stdid name subject year
1 arun chemistry 1996

2 alias maths 1996

if chemistry year 1996, then all subject in 1996 also not to display.

pls...


select stdid, name, subject, [year]
from
(
select *,sum(case when subject='chemistry' then 1 else 0 end) over (partition by [year]) as cnt
from student
)t
where cnt=0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2013-11-12 : 01:55:49

thank you so much visakh murukes sir ,i am so happy
for 2 days searching for this solution

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-12 : 02:00:36

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-12 : 02:01:05

Just FYI Tans solution should also work for you

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources