General information
Forum:
Microsoft SQL Server
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
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only