SQL Query that returns a "default" recordset

user_6067999 0
Viewed 65

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?

sql-server
2 Answers

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> )
user_11189780

Thank you Simonare. I wanted to handle this with a simple select and your solution was exactly what I needed. (I am posting a new answer because I can't figure out how to format code in a reply).

1) When records exists for a location like location = 1 I want those records.

2) When the records do not exist for a location I want the "default" records which are the records at Location = 0.

    -- Records for the location exists, return them
select * from Category Where Location = 1
union
select * from Category Where Location = 0 and not exists 
(select * from Category Where Location = 1)

-- Records for the location do not exists, return the default records (location=0)
select * from Category Where Location = 7
union
select * from Category Where Location = 0 and not exists 
(select * from Category Where Location = 7)
user_60679990
Related