Information générale
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
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement