[TriggaMike]

I'm doing a Query that finds classes that cannot be scheduled to have exams at the same time. The following code does just that, but the question I'm doing specifies that duplicates, such as "MATH215 ACCT201" and "ACCT201 MATH215" should not appear. I can't figure out how to make the query do what I'm asked. Here is my code and results:

SELECT 	DISTINCT c.course_Code AS "Course 1",
		x.course_Code AS "Course 2"
		FROM course_registration c JOIN course_registration x ON c.student_ID=x.student_ID
		WHERE 	c.semester ='1' AND
				x.semester ='1' AND
				c.year='1994' AND
				x.year='1994' AND
				c.course_code != x.course_code AND
				c.student_ID = x.student_ID
		ORDER BY c.course_code;

Course 1 Course 2 
ACCT210 CMPP201 
ACCT210 CMPP230 
ACCT210 ENGL201 
ACCT210 MATH215 
CMPP201 ACCT210 
CMPP201 CMPP230 
CMPP201 MATH215 
CMPP230 ACCT210 
CMPP230 CMPP201 
CMPP230 ENGL201 
CMPP230 MATH215 
CMPP230 MATH235 
ENGL201 ACCT210 
ENGL201 CMPP230 
MATH215 ACCT210 
MATH215 CMPP201 
MATH215 CMPP230 
MATH215 MATH235 
MATH235 CMPP230 
MATH235 MATH215 

This post has been edited by TriggaMike: 07 May 2009 - 05:35 PM

[nofear217]

Well since as you say it's MATH215 ACCT201 and ACCT201 MATH215...those are two separate records that do not match from a SQL Server Standpoint. It's like the difference between the database returning:

1 2
2 1

I could be wrong but I don't think straight SQL will accomplish what you want. You would need to do some massaging of the dataset once it's been pulled like iterating over each record and then examining both sides.

[AdaHacker]

There's really no easy way to do this in SQL, but it should still be possible. It's a bit long and complicated, but something akin to the following might do the trick. Of course, this is untested, as I don't have your data.

The idea is to run essentially the same query as a correlated subquery and add a check for the opposite pair of course numbers. So for each row, you run basically the same query on 2 new copies of the course_registration table, this time d and y instead of c and x, and insert the c.course_Code = y.course_Code AND x.course_Code = d.course_Code condition at the end of the WHERE clause to check for the opposite order of courses. The d.course_Code < y.course_Code condition forces an ordering, so that you'll still get one of the "duplicate" pairs in your result, but not the other - without it, you'd get neither.

SELECT DISTINCT c.course_Code AS "Course 1", x.course_Code AS "Course 2"
FROM course_registration c JOIN course_registration x ON c.student_ID=x.student_ID
WHERE c.semester ='1' AND
		   x.semester ='1' AND
				   c.year='1994' AND
				   x.year='1994' AND
	   c.course_code != x.course_code AND
		  c.student_ID = x.student_ID AND
		   NOT EXISTS (SELECT *
							  FROM course_registration d JOIN course_registration y ON d.student_ID=y.student_ID
							  WHERE d.semester ='1' AND
										 y.semester ='1' AND
												 d.year='1994' AND
												y.year='1994' AND
									d.course_code != y.course_code AND
									   d.student_ID = y.student_ID AND
									c.course_Code = y.course_Code AND
									x.course_Code = d.course_Code AND
									d.course_Code < y.course_Code)
ORDER BY c.course_code;

[TriggaMike]

That worked, thank you so much. I spent hours trying to figure it hour to no avail. This is supposed to be for an introductory class to database

[AdaHacker]

TriggaMike, on 7 May, 2009 - 06:35 PM, said:

This is supposed to be for an introductory class to database

Really? Then I wonder what the "correct" answer is, because that's by no means an introductory level query. In fact, I got that technique out of SQL For Smarties which is an advanced SQL programming book.