SQL Query that returns a "default" recordset

Question

I need a query that handles the concept of a "default recordset".

I need to get a set of default data if the requested records are not found when I run a query.

Here is an example:

Rec Category       Location 
--  --------       -------- 
1     Attendee        0   
2     Distributor     0
3     Sponsor         0
4     Attendee        1
5     Distributor     1
6     Sponsor         1
7     Attendee        2
8     Distributor     2
9     Sponsor         2

1. In case 1, the requested records are matched (e.g. SELECT * FROM Category WHERE Location = 1). This obviously returns records 4, 5, and 6.

2. In case 2, the query matches no records in the table, so I want default records instead; in this case, I want records 1, 2, and 3 as the default records. SELECT * FROM Category WHERE Location = 5 will find nothing and should therefore return those default records.

How would I make this happen?

Answer 1

Since the Requested Records is not clear. I ended up with simple approach. Please check the query below.

Select * from (
    select * from Category Where Location = 1
) t Where <Expression> = <Something>
union
select * from (
    select * from Category where Location = 5
) t2 Where <Expression> != <Something>

If your requested Records part is another select statement you can use

Select * from (
    select * from Category Where Location = 1
) t Where Exists (Select 1 from tablex WHERE <Expression> = <Something> )
union
select * from (
    select * from Category where Location = 5
) t2 Where NOT Exists (Select 1 from tablex WHERE <Expression> = <Something> )