Find missing associations - 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 2008 Forums

Transact-SQL (2008)

Find missing associations

Author

Topic

reidkell
Starting Member

16 Posts

Posted - 2014-05-07 : 18:52:15

Using a simplified analogy, I'm trying to figure out how to write the SQL to do the following:

Table1: People
Table2: Courses
Table3: People/Courses Associations (junction)

All people are assigned certain courses (differs among people). Say there are courses 1 thru 100 and Fred should be assigned Courses 2 and 5 (hardcoded or lookup WHERE values). I need to know if Fred has NOT been associated with either Course2 or Course5 AND display which one(s) is/are missing. For example, if nobody signed Fred up for either of his required courses (in Association table), I need the output to show:

Person.......Missing Course
Fred.........Course2
Fred.........Course5

Hope this makes sense. Not sure how to do this. Any input appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-08 : 02:26:27

Sample data and the corresponding expected result please

KH
[spoiler]Time is always against us[/spoiler]

reidkell
Starting Member

16 Posts

Posted - 2014-05-08 : 12:42:33

Using a simplified analogy, I'm trying to figure out how to write the SQL to do the following:


Table1: People
PersonID	PersonName	DeptID
201		Fred		32A
209		Julie		25C
255		Jane		19F
262		John		55R

Table2: Courses
CourseID	CourseName
1		Course1
2		Course2
3		Course3
4		Course4
5		Course5
N		CourseN

Table3: People/Courses Associations (junction)
PersonID	CourseID
209		1
209		2
209		5
255		1
255		3
262		1
262		5

Everyone in department 32A and 19F should be associated with Courses 1 and 5 (ad-hoc criteria values). I need to know who is NOT associated with Courses 1 and/or 5 AND display the missing info. In the example above, Fred has no assigned courses and Jane is missing Course5. I need the output as follows:


Person		Missing Course
Fred		Course1
Fred		Course5
Jane		Course5

Hope this makes sense. Not sure how to do this. Any input appreciated.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-08 : 13:29:34

Here is one way (notice how the sample data is in a consumable format):

-- Setup Sample Data
DECLARE @People TABLE (PersonID	INT, PersonName VARCHAR(20), DeptID VARCHAR(20))

INSERT @People VALUES
(201, 'Fred', '32A'),
(209, 'Julie', '25C'),
(255, 'Jane', '19F'),
(262, 'John', '55R')

DECLARE @Courses TABLE (CourseID INT, CourseName VARCHAR(20))

INSERT @Courses VALUES
(1, 'Course1'),
(2, 'Course2'),
(3, 'Course3'),
(4, 'Course4'),
(5, 'Course5')


DECLARE @PeopleCourses TABLE (PersonID INT, CourseID INT)
INSERT @PeopleCourses VALUES
(209, 1),
(209, 2),
(209, 5),
(255, 1),
(255, 3),
(262, 1),
(262, 5)


-- Return results
SELECT 
	*
FROM
	(
		SELECT 
			*
		FROM
			@People AS People
		CROSS JOIN
			@Courses AS Courses
		WHERE
			People.DeptID IN ('32A', '19F')
			AND Courses.CourseName IN ('Course1', 'Course5')
	) AS P
LEFT OUTER JOIN
	@PeopleCourses AS PeopleCourses
	ON P.PersonID = PeopleCourses.PersonID
	AND P.CourseID = PeopleCourses.CourseID
WHERE
	PeopleCourses.PersonID IS NULL

reidkell
Starting Member

16 Posts

Posted - 2014-05-08 : 15:51:50

Wow, THANK YOU! I totally forgot about CROSS JOIN and would not have been able to work this out. This is perfect. Definitely going to learn from this. MUCH appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources