How to create multiple selects if the preceeding value is NULL

Help! I am trying to create a query that will perform 'Fallback' queries if
the preceeding value is NULL. and then change the result to display fixed
text (like using the CASE statement) eg:
The first select is select name from guest where room='123' and status='I'
If there are no names found, I want it to run the next statement
select code from OOO where room='123' .If there are results for this, I
would like SQL to change the result 'code' and put text 'OUT OF ORDER'.
If there are no results for the first or second selects, to put the text
'VACANT' in the field.
I am using curse and @.DECLARES, where the room will be declared with the
curse.
The Columns and tables are;
Table Column
Z_rooms code varchar(6)
guest name varchar (70)
outoforder code varchar (6)
At the moment it looks something like this, albeit an abridged version
DECLARE @.guest_name varchar (70)
DECLARE @.room_code varchar (6)
declare curse cursor
for select code from z_rooms where property='NEWCH' (This will declare the
room_code
open curse
fetch next from curse into @.room_code
while (@.@.fetch_status<>-1)
begin
if (@.@.fetch_status<>-2)
begin
Output should look like
room guest_name
101 MOUSE, MR MICKEY (if there is a name, this is an exampl
e)
101 OUT OF ORDER (if there is no guest name, but OOO code is
not null)
101 VACANT (if there is no guest name OR OOO code found)
I hope this helps clarify what I am trying to doWhy are you using a cursor to do this? Always try to avoid cursors. It
looks like a single SELECT statement will give the result you want:
SELECT room,
COALESCE(guest_name,
CASE
WHEN outoforder.code IS NOT NULL
THEN 'OUT OF ORDER'
WHEN outoforder.code IS NULL
THEN 'VACANT'
END)
FROM z_rooms
JOIN guest
ON ? /* unspecified */
JOIN outoforder
ON ? /* unspecified */
David Portas
SQL Server MVP
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Here is a wild guess:
SELECT room_nbr,
CASE WHEN guest_name IS NOT NULL THEN guest_name
WHEN guest_name IS NULL AND room_status = 0
THEN 'out of order'
ELSE 'vacant' END AS guest_or_room_status
FROM Hotel
WHERE room_nbr = @.my_room;
You might want to read a book on data modeling so that you will not
have a column drawn from two different domains in a table. And none
of those names were very helpful in any case.