no duplicates - but not the normal no dup question

[deanbri75]

First a little background: I have an online applicaiton where incoming freshmen apply for campus housing. Part of the app lets them request their rommmate. The roommate requests have to be mutual. (We do random assignments for those that don't request a particular person but that's a different query.) This is an SQL database and i'm using Access 2003 on the front end. I'm open to any options this is just what i've been working with.

Anyway, here's my code

SELECT DISTINCT a.FirstName, a.LastName, b.FirstName AS Expr2, b.LastName AS Expr3, a.PriorityDate, b.PriorityDate AS Expr6
FROM         dbo.PostApp a LEFT OUTER JOIN
                      dbo.PostApp b ON a.ID = b.RoommateID AND b.ID = a.RoommateID AND a.Gender = b.Gender
WHERE     (a.PriorityDate IS NOT NULL) AND (b.PriorityDate IS NOT NULL)
ORDER BY a.PriorityDate

The results give me pretty much what I want except you get duplicates - One row where for A's app requesting B and one for B's app requesting A.

Results:

FirstName	LastName	Expr2		Expr3		PriorityDate	Expr6
Cameron		Carr		Donna		Saunders	7/27/2005	7/27/2005
Donna		Saunders	Cameron		Carr		7/27/2005	7/27/2005
Christine	Sirna		Kara		Wilson		7/28/2005	8/2/2005
Kara		Wilson		Christine	Sirna		8/2/2005	7/28/2005

I basicly only need every other row but am not sure how to do that. I tried experimenting with NOT IN but didn't have much luck.

thanks,
brian

 

[r937]

try adding

AND a.ID < b.ID

to the WHERE clause

oh, and you might as well change it from LEFT OUTER to INNER (because that's what you really want), although this has nothing to do with eliminating the dupes

r937.com | rudy.ca

 

[deanbri75]

Thanks so much 937 for the quick response. It did just the trick.

I've inherited this project and will probably be posting a lot of questions on here as I work through all the nuances of it.

It's kind of like in Star Wars when Luke ran off to fight Darth Vader. Sure, he had the force but wasn't really ready so he got his arm chopped off.

I'm like Luke, SQL knowledge is like the force and this project is the dark lord of the sith (but luckily not my father). Hopefully I won't lose a limb in the process!