excluding records in a 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

SQL Server 2005 Forums

Transact-SQL (2005)

excluding records in a query

Author

Topic

ggardei
Starting Member

6 Posts

Posted - 2010-05-17 : 11:34:59

Greetings,

I wrote a query that lists all the assignment categories in a give class and what the weight of each category is...

SELECT
glossary.displaytext AS Category,
CategoryWeight,
AssignmentCategoryID as SRCourseTestAssignCatID
FROM dbo.SRCourseTestAssignCat
JOIN glossary ON dbo.SRCourseTestAssignCat.AssignmentCategoryID = dbo.Glossary.UniqueId

WHERE SROfferID = 7491

which returns the results of:

Category CategoryWeight SRCourseTestAssignCatID
Tests 35 2995
Reading 30 2997
Papers 30 2991
Project 5 2990

I want to exclude any of the categories that do not have graded assignments . I wrote this query...

SELECT
AssignmentCategoryID as SRCourseTestAssignCatID
FROM dbo.SRCourseTestAssignCat

WHERE SROfferID = 7491

INTERSECT

SELECT DISTINCT
SRCourseTestAssignCatID
FROM SRCourseGrade
JOIN dbo.SRCourseTest ON dbo.SRCourseTest.SRCourseTestID = SRCourseGrade.SRCourseTestID
WHERE SROfferID = 7491

which returns

2991
2995
2997

Now, I need to figure out how to get the category name and weight. I have not been able to figure this one out. Any help!

I want the following results

Tests 35 2995
Reading 30 2997
Papers 30 2991

George W. Gardei
God's Bible School & College
(513) 763-6516
1810 Young Street
Cincinnati, OH. 45202

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-17 : 11:46:38

[code]SELECT
glossary.displaytext AS Category,
CategoryWeight,
AssignmentCategoryID as SRCourseTestAssignCatID
FROM dbo.SRCourseTestAssignCat
JOIN glossary ON dbo.SRCourseTestAssignCat.AssignmentCategoryID = dbo.Glossary.UniqueId

WHERE SROfferID = 7491
AND AssignmentCategoryID IN (SELECT
AssignmentCategoryID as SRCourseTestAssignCatID
FROM dbo.SRCourseTestAssignCat

WHERE SROfferID = 7491

INTERSECT

SELECT DISTINCT
SRCourseTestAssignCatID
FROM SRCourseGrade
JOIN dbo.SRCourseTest ON dbo.SRCourseTest.SRCourseTestID = SRCourseGrade.SRCourseTestID
WHERE SROfferID = 7491 )
[/code]

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

ggardei
Starting Member

6 Posts

Posted - 2010-05-17 : 13:18:51

Thank you very much!!!

this is close to what I came up with. I will compare to see what I did wrong.

George W. Gardei
God's Bible School & College
(513) 763-6516
1810 Young Street
Cincinnati, OH. 45202

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-18 : 01:10:01

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources