This not That Query - 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

This not That Query

Author

Topic

shunut
Starting Member

2 Posts

Posted - 2011-09-25 : 13:04:55

I'm very new to SQL/MySQL and I'm trying to write a query that returns one subject, but not another. I've been looking around but I just can't seem to find what I need to make this work, everything I try returns an error. Here is what I want to do:

I have a table where subjects are ordered by years. Each subject is listed individually (in its own row). There can be multiple rows for a single year, example:

2011 - subject A
2011 - subject B
2011 - subject C
2010 - subject B
2010 - subject C
2009 - subject A
2009 - subject C
.
.
.

What I would like is to show the years that contain subject A but do not contain subject B. So for the short table I have listed above, the only year that the query would return is 2009.

Any help would be greatly appreciated.

Josh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-25 : 13:09:13

[code]
SELECT Year
FROM Table
WHERE Subject IN ('Subject A','Subject B')
GROUP BY Year
HAVING MIN(Subject) = MAX(Subject)
AND MIN(Subject) = 'subject A'
[/code]

the above code works in MS SQL Server. this is a MS SQL Server forum so solutions given are mostly SQL Server specific. Try if above works in MySQL. If not, please try your luck at some MySQL forums like www.dbforums.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-25 : 18:06:44

[code]SELECT Year
FROM dbo.Table
WHERE Subject IN ('Subject A', 'Subject B')
GROUP BY Year
HAVING MAX(CASE WHEN Subject = 'Subject A' THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN Subject = 'Subject B' THEN 1 ELSE 0 END) = 0[/code]

N 56°04'39.26"
E 12°55'05.63"

shunut
Starting Member

2 Posts

Posted - 2011-09-25 : 20:18:43

Both worked great! Thank you very much, I've been reading tutorials and trying stuff for days. Now with the code, I can compare it to tutorials and it will be way easier to figure out how and why it worked.

THANK YOU SO MUCH!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 00:28:34

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources