August 9, 2010 at 10:47 am
Hi, I don't know if this is possible, and I do know it would return a LOT of rows. But does anyone know how the retrieve the inverse of this query? I'm looking to retrieve everyone who isn't currently registered in each course.
DROP TABLE TB__EmpCourse
DROP TABLE TB__Employees
DROP TABLE TB__Courses
CREATE TABLE TB__Courses
(
CourseID INT IDENTITY(1,1) CONSTRAINT PK_Course PRIMARY KEY CLUSTERED,
CourseName NVARCHAR(1000) NOT NULL
)
CREATE TABLE TB__Employees
(
EmployeeID INT IDENTITY(1,1) CONSTRAINT PK_Employees PRIMARY KEY CLUSTERED,
FirstName NVARCHAR(200) NOT NULL,
LastName NVARCHAR(200) NOT NULL,
Location NVARCHAR(500) NULL
)
CREATE TABLE TB__EmpCourse
(
EmpCourseID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_EmpCourses PRIMARY KEY CLUSTERED,
EmployeeID INT NOT NULL CONSTRAINT FK_EmpCourseToEmployees FOREIGN KEY (EmployeeID) REFERENCES TB__Employees(EmployeeID),
CourseID INT NOT NULL CONSTRAINT FK_EmpCourseToCourses FOREIGN KEY (CourseID) REFERENCES TB__Courses(CourseID),
DateTaken DATETIME NOT NULL,
DateExpired DATETIME NULL
)
INSERT INTO TB__Employees (FirstName,LastName,Location) VALUES ('First1','Last1','Loc1')
INSERT INTO TB__Employees (FirstName,LastName,Location) VALUES ('First2','Last2','Loc2')
INSERT INTO TB__Employees (FirstName,LastName,Location) VALUES ('First3','Last3','Loc3')
INSERT INTO TB__Courses (CourseName) VALUES ('Course1')
INSERT INTO TB__Courses (CourseName) VALUES ('Course2')
INSERT INTO TB__Courses (CourseName) VALUES ('Course3')
INSERT INTO TB__EmpCourse (EmployeeID,CourseID,DateTaken,DateExpired) VALUES (1,1,Getdate(),NULL)
INSERT INTO TB__EmpCourse (EmployeeID,CourseID,DateTaken,DateExpired) VALUES (1,2,Getdate(),NULL)
INSERT INTO TB__EmpCourse (EmployeeID,CourseID,DateTaken,DateExpired) VALUES (2,1,Getdate(),NULL)
INSERT INTO TB__EmpCourse (EmployeeID,CourseID,DateTaken,DateExpired) VALUES (2,3,Getdate(),NULL)
SELECT CourseName
,FirstName
,LastName
,Location
,DateTaken
,DateExpired
FROM dbo.TB__EmpCourse sec
inner join dbo.TB__Employees se on se.EmployeeID = sec.EmployeeID
inner join dbo.TB__Courses sc on Sc.courseid = sec.courseid
Thanks for any help,
Mike
August 9, 2010 at 10:55 am
August 9, 2010 at 11:18 am
Thanks for the tip, realized i had a messup in the last query I posted. Hope this one is useful.
August 9, 2010 at 11:30 am
i think this is what you want...a LEFT OUTER JOIN will help identify the ID's not int he realtionship table:
SELECT se.* FROM dbo.TB__Employees se
LEFT OUTER JOIN dbo.TB__EmpCourse sec
ON se.EmployeeID = sec.EmployeeID
WHERE sec.EmployeeID IS NULL
--null in EmpCourse table means the employee is not in a course.
Lowell
August 9, 2010 at 11:36 am
Hi Lowell,
To be more specific, I'd like to retrieve all of the people not in a course.
So where this retrieves all of the people in Course 3:
SELECT CourseName
,FirstName
,LastName
,Location
,DateTaken
,DateExpired
FROM dbo.TB__EmpCourse sec
INNER JOIN dbo.TB__Employees se ON se.EmployeeID = sec.EmployeeID
INNER JOIN dbo.TB__Courses sc ON Sc.courseid = sec.courseid
WHERE SEC.CourseID = 3
I'd like another that retrieved all of the people that aren't in course 3.
August 9, 2010 at 12:33 pm
still requires a LEFT OUTER JOIN to get the specific data: here's just one way to do it:
SELECT se.* FROM dbo.TB__Employees se
LEFT OUTER JOIN (
SELECT
sec.EmployeeID
FROM dbo.TB__EmpCourse sec
INNER JOIN dbo.TB__Employees se ON se.EmployeeID = sec.EmployeeID
INNER JOIN dbo.TB__Courses sc ON Sc.courseid = sec.courseid
WHERE SEC.CourseID = 3) sec --same alias
WHERE sec.EmployeeID IS NULL
I'd like another that retrieved all of the people that aren't in course 3.
Lowell
August 9, 2010 at 1:11 pm
Thanks Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply