Categories: MSDN / DotNet / Java / Scripts / Linux / PHP Ask - La ask - La Answer
Programmers Tech >> Developer Tech >> Database

Getting all items not listed in a table

I have 2 tables

The first table lists all instances of a school

The second lists all reports filed and the year, and schoolID they were filed for

I want to know how I can create a SQL query that will give me all of the schools that are not in the second table for a given year(all the school that have yet to submit a report).

Pretend these are the Tables

Schools:
School ID
School Name

Reports:
ReportID
SchoolID
ReportYear

I want to show all of the schoolNames

Thanks
[564 byte] By [partyk1d24] at [2007-11-11 9:56:27]
«« How to install GCC in WindowsXP
»» Form loading issue
# 1 Re: Getting all items not listed in a table
If you're using MS Access or SQL Server, try this:

SELECT SchoolName
FROM Schools
WHERE Schools.ID NOT IN
(SELECT DISTINCT SchoolID
FROM Reports
WHERE ReportYear = "2006")
Phil Weber at 2007-11-11 23:43:37 >
# 2 Re: Getting all items not listed in a table
OOP's i think you meant this:
SELECT [School Name]
FROM Schools
WHERE [School ID] NOT IN
(SELECT DISTINCT SchoolID
FROM Reports
WHERE ReportYear = "2006")
Ron Weller at 2007-11-11 23:44:37 >
Developer Tech
Database

Developer Tech

All Classified

Developer Tech New Topic

Developer Tech Hot Topic