CODEWITHSUNDEEP
sqlsql-server
Stl
Stl

Reputation: 13

Excluding results

Course (one table) and module (another table). Modules can be part of different courses. Joined in this table called coursemodule 

CourseCode  ModuleID
       BS   BS2029
       CN   CN5485
       CS   CN5485
       BS   CS1004
       CN   CS1004
       CS   CS1004
       CS   CS2017
       BS   CS2026
       CS   CS2026

I want to select the modules that appear in both the CS and CN courses but not ones that also appear in the BS course. If I run this:

SELECT m.ModuleID, m.ModuleDescription
FROM Module as m
INNER JOIN (SELECT coursecode, ModuleID 
            FROM CourseModule      
            WHERE CourseCode = 'CS') AS CodeCS
ON m.ModuleID = CodeCS.ModuleID            
INNER JOIN (SELECT coursecode, ModuleID 
            FROM CourseModule      
            WHERE CourseCode = 'CN') AS CodeCN
ON m.ModuleID = CodeCN.ModuleID

I get:

ModuleID        ModuleDescription
CN5485          Managing Networks
CS1004          Introduction to Programming

which based on this query is correct but I only want to return CN5485 as CS1004 is also in the BS course.

Tried not in, <>, except variations all with terrible success! What do I need to be adding? amending in the query?

Upvotes: 0

Views: 62

Answers (4)

Brian Ball
Brian Ball

Reputation: 12596

The simplest way I can think of would be to use 3 queries. The first query will return all modules that have CS courses, then Intersect with a query that selects all modules that have CN courses. Take the result of the Interact and then run an Except against Moudles that are in BS courses.

So a simplified version of the query would look like this:

<Query to pull distinct Modules that have a CS course>
Intersect
<Query to pull distinct Modules that have a CN course>
Except
<Query to pull distinct Modules that have a BS course>

Updated query for Seetal's comment

Select ModuleId, ModuleName
From Module
Where ModuleID IN (
  <Query to pull distinct ModuleID that have a CS course>
  Intersect
  <Query to pull distinct ModuleID that have a CN course>
  Except
  <Query to pull distinct ModuleID that have a BS course>
)

Upvotes: 0

TheVillageIdiot
TheVillageIdiot

Reputation: 40497

Well don't know what version of SQL Server you are using. Try following (except) clause if your version supports this:

SELECT m.ModuleID, m.ModuleDescription
FROM Module as m
INNER JOIN CourseModule cm ON (m.ModuleID = cm.ModuleID 
               AND cm.CourseCode IN ('CS', 'CN'))
EXCEPT --
SELECT m.ModuleID, m.ModuleDescription
FROM Module as m
INNER JOIN CourseModule cm ON (m.ModuleID = cm.ModuleID 
               AND cm.CourseCode IN ('BS'))

You can try seleting ModuleIDs and then descriptions.

Upvotes: 0

Marcin Zablocki
Marcin Zablocki

Reputation: 10683

You can try this query - it's easy to read and uderstand:

SELECT m.ModuleID, m.ModuleDescription
FROM Module as m
WHERE m.ModuleID in 
((SELECT ModuleID FROM CourseModule WHERE CourseCode = 'CN'
INTERSECT
SELECT ModuleID FROM CourseModule WHERE CourseCode = 'CB')
EXCEPT
SELECT ModuleID FROM CourseModule WHERE CourseCode = 'BS')

Upvotes: 0

Joe Enos
Joe Enos

Reputation: 40383

I'd probably go with something a little different - since you want two "in" and one "not in", I'd write it that way, which would make it clear what you're doing:

SELECT m.ModuleID, m.ModuleDescription
FROM Module as m
WHERE m.ModuleID in (SELECT ModuleID FROM CourseModule WHERE CourseCode = 'CS')
AND m.ModuleID in (SELECT ModuleID FROM CourseModule WHERE CourseCode = 'CN')
AND m.ModuleID not in (SELECT ModuleID FROM CourseModule WHERE CourseCode = 'BS')

The in syntax in my opinion is a little clearer than joining - joining to me implies that you want something from that table, when in fact you just want to make sure a match exists, not retrieve anything from it. Also joins can get you in trouble with duplicates if you're not careful - in won't.

Upvotes: 1

Related Questions