SubSelect question 2

[IAMINFO]

Hello everyone,

I am trying write a sql statement that would give me the student id for everyone in the room.

There are 3 students in a room , I know the student ID of 1
student.

I believe I need to compare the movein and moveout dates
with this (Student = 10056936), not really sure how to proceed. Please help if you can, thank you for taking time.



I have the students movein date and moveout date.



SELECT Student, Building,Dorm,Room, MOVEIN, MOVEOUT

FROM pps_housingchanges
WHERE (Student = 10056936)

 

[markros]

If you want to find all students currently in the room, then

declare @today datetime
set @today = getdate()

SELECT     Student, Building,Dorm,Room, MOVEIN,  MOVEOUT
                     
FROM         pps_housingchanges
WHERE     (Student = 10056936) where MoveIn <=@Today and MoveOut IS NULL -- Students moved in and never moved out

 

[gmmastros]

I would approach this by using a self join. Basically, join the table back to itself using a derived table syntax.

First, get the building, Dorm, and Room for the student you know. Then, join back to the table again based on those 3 conditions to get all your students that share the same room.

Like this...

Select pps_housingChanges.*
From   pps_housingchanges
       Inner Join (
         Select Building, Dorm, Room
         From   pps_housingchanges
         Where  Student = 10056936
         ) as A
         On  pps_housingchanges.Building = A.Building
         And pps_housingchanges.Dorm = A.Dorm
         And pps_housingchanges.Room = A.Room
Where  pps_housingchanges.MoveIn <= GetDate()
       And (pps_housingchanges.MoveOut Is NULL Or pps_housingchanges.MoveOut > GetDate())

Alternatively, you could create 3 variables and set those based on the one student you know, and then use that in the Where clause (instead of student).

Like this:

SET NOCOUNT ON

Declare @Building VarChar(20)
Declare @Dorm VarChar(20)
Declare @Room VarChar(20)

Select @Building = Building, 
       @Dorm = Dorm, 
       @Room = Room
From   pps_housingchanges
Where  Student = 10056936

Select pps_housingChanges.*
From   pps_housingchanges
Where  pps_housingchanges.Building = @Building
       And pps_housingchanges.Dorm = @Dorm
       And pps_housingchanges.Room = @Room
       And pps_housingchanges.MoveIn <= GetDate()
       And (pps_housingchanges.MoveOut Is NULL Or pps_housingchanges.MoveOut > GetDate())

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Aha - the idea was to get all the students sharing the same room, now I see.

 

[IAMINFO]

thank you gmmastros and thank you markros
I really appreciate the time you take. Thanks again