Firebird Subquery
Good day
I would like to know how can I do the following subquery.
I have two tables and my data looks like this
Table A
Suite [PK]
Week [PK]
RoomStatus
Table B
Suite [FK]
Week [FK]
HomeRoom
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.
Thank you
Henry
I would like to know how can I do the following subquery.
I have two tables and my data looks like this
Table A
Suite [PK]
Week [PK]
RoomStatus
Table B
Suite [FK]
Week [FK]
HomeRoom
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.
Thank you
Henry
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
so if they have a homeroom you want the status for that, if they don't you want what? (null, status of room they are in, something else)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I already have it in a store procedure to do the additional sql, the idea of yours wont work 100% because there could be rooms empty or in maintenance and then they dont have a homeroom.
thanx
henry