Basic SQL Statement Question

[t8ntboy]

I have two tables, Sections and Enrollments. The two tables can be
joined on a key. The records for the enrollment table are imported
from another system that doesn't always match what is stored in the
sections table. For example, there may be a section or two in the
enrollments table that does not exist in the sections table.

I am trying to create a report that will yield a list of the
enrollment data that do not match/join a section. How can this be
done via an SQL statement? Essentially I am trying to figure out what
sections are missing based on unmatched enrollment data.

Please help.

[Plamen Ratchev]

Re: Basic SQL Statement Question

On SQL Server 2005 you can use the EXCEPT operator (assuming the key column
is section_id):

SELECT section_id
FROM Enrollments
EXCEPT
SELECT section_id
FROM Sections;

Or use NOT EXISTS:

SELECT section_id
FROM Enrollments AS E
WHERE NOT EXISTS
(SELECT *
FROM Sections AS S
WHERE S.section_id = E.section_id);

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[Philipp Post]

Re: Basic SQL Statement Question

The same using JOIN:

SELECT enrollment_id,
E1.section_id,
enrollment_name
FROM Enrollments AS E1
LEFT JOIN Sections AS S1
ON E1.section_id = S1.section_id
WHERE S1.section_id IS NULL -- just show unmatched rows

brgds

Philipp Post