Limit number of results in joined table

[deanbri75]

I have a View that produces a one-to-many result list. What I need it to do is limit the "many" part to 5 instead of listing every possible result. Here's my query:

SELECT     TOP 100 percent a.GID AS Roommate1, b.GID AS Potential_Roommate2
FROM         dbo.APPLICATION a INNER JOIN
                      dbo.APPLICATION b ON a.MATCH1 = b.MATCH1 AND a.MATCH2 = b.MATCH2 AND a.MATCH3 = b.MATCH3 AND a.GID <> b.GID INNER JOIN
                      dbo.ADMISSIONS c ON a.GID = c.GID INNER JOIN
                      dbo.ADMISSIONS d ON b.GID = d.GID AND c.GENDER = d.GENDER
WHERE     (NOT (a.PRIORITY_DATE IS NULL)) AND (NOT (b.PRIORITY_DATE IS NULL))
ORDER BY a.GID, b.GID

And these are the results I get:
Roommate1 Potential_Roommate2
AAB0001 ABB0001
AAB0001 ACL0001
AAB0001 ACM0003
AAB0001 ADS0001
AAB0001 AEB0002
AAB0001 AEC0001
AAB0001 AED0001
AAB0001 AEE0001
AAB0001 AEH0001
AAB0001 AEV0001
. . . . <snip> hundres of records

AKB0001 ces0004
AKB0001 cgh0001
AKB0001 CHs0001
AKB0001 CJK0002
AKB0001 CJM0002
AKB0001 ckf0001
AKB0001 CKZ0001
AKB0001 cln0001
AKB0001 CLY0001
. . . . . <snip> hundreds more

What I want to do is limit the results for Roommate1 to the top 5 instead of every single one.

 

[rac2]

Possibly this

SELECT a.GID AS Roommate1, 
       b.GID AS Potential_Roommate2, 
       c.GID AS Potential_Roommate3, 
       d.GID AS Potential_Roommate4, 
       e.GID AS Potential_Roommate5 

FROM dbo.APPLICATION a 
LEFT JOIN dbo.APPLICATION b ON 
         a.MATCH1 = b.MATCH1 
     AND a.MATCH2 = b.MATCH2 
     AND a.MATCH3 = b.MATCH3 
     AND a.GENDER = b.GENDER
     AND a.GID <> b.GID 

LEFT JOIN dbo.APPLICATION c ON 
         a.MATCH1 = c.MATCH1 
     AND a.MATCH2 = c.MATCH2 
     AND a.MATCH3 = c.MATCH3 
     AND a.GENDER = c.GENDER
     AND a.GID <> b.GID 
     AND a.GID <> c.GID 

LEFT JOIN dbo.APPLICATION d ON 
         a.MATCH1 = d.MATCH1 
     AND a.MATCH2 = d.MATCH2 
     AND a.MATCH3 = d.MATCH3 
     AND a.GENDER = d.GENDER
     AND a.GID <> b.GID 
     AND a.GID <> c.GID 
     AND a.GID <> d.GID 

LEFT JOIN dbo.APPLICATION e ON 
         a.MATCH1 = e.MATCH1 
     AND a.MATCH2 = e.MATCH2 
     AND a.MATCH3 = e.MATCH3 
     AND a.GENDER = e.GENDER
     AND a.GID <> b.GID 
     AND a.GID <> c.GID 
     AND a.GID <> d.GID 
     AND a.GID <> e.GID 

WHERE (NOT (a.PRIORITY_DATE IS NULL)) 
  AND (NOT (b.PRIORITY_DATE IS NULL))

ORDER BY a.GID, b.GID

 

[rac2]

Or this

SELECT a.GID AS Roommate1,
       b.GID AS Potential_Roommates

FROM dbo.APPLICATION a
LEFT JOIN (
   SELECT TOP 5 GID
   FROM dbo.APPLICATION
   WHERE a.MATCH1 = MATCH1
     AND a.MATCH2 = MATCH2
     AND a.MATCH3 = MATCH3
     AND a.GENDER = GENDER
     AND a.GID <> GID 
     AND (NOT (PRIORITY_DATE IS NULL))
  ) b
     
WHERE (NOT (a.PRIORITY_DATE IS NULL))
  AND 

ORDER BY a.GID, b.GID

 

[deanbri75]

I ended up reading a bunch of TSQL stuff on MSDN and found that a cursor was what I needed. It's fabulous dealing with one to many situations on a record by record basis.

DECLARE @RM1 VARCHAR(50), @RM2 VARCHAR(50)

DECLARE RM_CURSOR CURSOR FOR

SELECT     TOP 100 PERCENT a.GID AS RM1, b.GID AS RM2
FROM         dbo.APPLICATION a INNER JOIN
                      dbo.APPLICATION b ON a.MATCH1 = b.MATCH1 AND a.MATCH2 = b.MATCH2 AND a.MATCH3 = b.MATCH3 AND a.GID <> b.GID AND 
                      a.BUILDING_PREF1 = b.BUILDING_PREF1 AND a.BUILDING_PREF2 = b.BUILDING_PREF2 AND a.BUILDING_PREF3 = b.BUILDING_PREF3 INNER JOIN
                      dbo.ADMISSIONS c ON a.GID = c.GID INNER JOIN
                      dbo.ADMISSIONS d ON b.GID = d.GID AND c.GENDER = d.GENDER
WHERE     (NOT (a.PRIORITY_DATE IS NULL)) AND (NOT (b.PRIORITY_DATE IS NULL)) AND (a.GID NOT IN
                          (SELECT     RM1
                            FROM          DBO.ROOMMATE_MATCH)) AND (a.GID NOT IN
                          (SELECT     RM2
                            FROM          DBO.ROOMMATE_MATCH)) AND (NOT (b.GID IN
                          (SELECT     RM1
                            FROM          DBO.ROOMMATE_MATCH))) AND (NOT (b.GID IN
                          (SELECT     RM2
                            FROM          DBO.ROOMMATE_MATCH)))
ORDER BY a.GID, b.GID;

OPEN RM_CURSOR;

FETCH NEXT FROM RM_CURSOR
INTO @RM1, @RM2;

WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO dbo.ROOMMATE_MATCH (RM1, RM2) VALUES (@RM1, @RM2)
		FETCH NEXT FROM RM_CURSOR
		INTO @RM1, @RM2;
	END;
CLOSE RM_CURSOR;
DEALLOCATE RM_CURSOR;

I'm sure it's not the most elegent way to do this but it works like a charm.