General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
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
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