I would like to know how can I do the following subquery.
I have two tables and my data looks like this
example of data in table A
Suite Week RoomStatus
A 1 OPEN
B 2 OPEN
C 3 CLOSE
Example of data in table B
Suite Week HomeRoom
A 1 A
B 1 C
C 1 B
Now in my select I want to select everything from table B, but I want to get for each row the person actual RoomStatus from table a.
for example row 1 in table B, the guest HomeRoom = the Suite where he is currently in,
but row two The guest are in Room B but his HomeRoom is C ( I need now in this select his RoomStatus from table a)
If possible I want to use a subquery to perform this select because I need to perform a lot of additional sql once I got the data.