Amazing. Simple amazing. :) I hope I see you at the GLGDW to thank you personally for all your help. Not only for this one but for other times too.
>Here's almost complete version of the query. For simplicity I used only one key field "Room". It shouldn't be hard to add "hall" and "period".
SELECT
> s11.lastname AS lastname1,
> s11.firstname AS firstname1,
> s12.lastname AS lastname2,
> s12.firstname AS firstname2,
> s11.room
> FROM (
>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) d2
> JOIN Students s11 on d2.sid1 = s11.StudentId And d2.room = s11.room
> LEFT JOIN Students s12 on d2.sid2 = s12.StudentId And d2.room = s12.room
>
I'm not sure if it's "the best" solution but it produces correct result.
>
>>I posted a message on the VFP forum but I think I might have better luck here.
>>
>>I have a table of students. In this table, there is a name and room they are staying in.
>>
>>I want a query that returns one row for each room that has 2 fields in that row of the names of both people staying in that room. There can only 0, 1 or 2 people in a room. So if one person was in a room, it would return null for the other name.
>>
>>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:
>>
>>Name1 Name2 Room
>>XXX YYY 101
>>YYY XXX 101
>>
>>I just want one or the other, not 2 for each room. Any ideas? TIA
>>
>>
>>Eric Stephani
