The following simplified query will return the list of rooms with student id's. You can use it as starting point for your query.

SELECT room, sid1, 
		CASE WHEN sid2<>sid1 THEN sid2 ELSE Null END AS sid2 
	FROM (
SELECT DISTINCT s1.Room, 
	(SELECT TOP 1 StudentId FROM students s2
		WHERE s2.room = s1.room
		ORDER BY StudentId) AS Sid1,
	(SELECT TOP 1 StudentId FROM students s3
		WHERE s3.room = s1.room
		ORDER BY StudentId DESC) AS sid2
	FROM students s1) d1

>I have a table of students. In this students table, I have a name and room they are staying in.
>
>I want a query that returns one row for each room that has as fields in that row the names of both people staying in that room.
>
>There can only 0, 1 or 2 people in a room.
>
>Im using a sql server as a backend so if the query can run on there that would be nice, otherwise I can just select all the students and then use vfp to to the query.
>
>This is what I have so far.
>
>select s1.lastname, s1.firstname, s2.lastname as lastname2, s2.firstname as firstname2, s1.room
>from students s1 inner join students s2 on s1.hall=s2.hall and s1.room=s2.room and s1.periodid=s2.periodid and s1.studentid<>s2.studentid
>where s1.hall = 'BREESE' and s1.periodid=4
>order by s1.room
>
>This almost works, However there is 2 records for each room. The difference between them is the names. Like this:
>
>XXX YYY 101
>YYY XXX 101
>
>I just want one or the other, not 2 for each room. How can I do this?
>
>
>Eric Stephani