quote:

---

Originally posted by kpgraci

I have a table like this:

Table: Plans
--------------
PlanName varchar(50)
LOB integer

FYI: LOB stands for 'Line of Business' and indicates the kind of insurance policy

LOB can be 1,2,3...for a specific LOB or -1 for 'all others'

I want to query this table and return all LOBs that match a givien LOB, and if none match, then return all LOBs that are equal to -1

So if the table were as follows:

Planname LOB
--------------
Plan1 1
Plan2 1
Plan3 2
Plan4 3
Plan5 -1
Plan6 -1

then the query for LOB=1 should return

Planname LOB
--------------
Plan1 1
Plan2 1

and the query for LOB=7 should return

Planname LOB
--------------
Plan5 -1
Plan6 -1

also, if LOB is not found and there are no LOB -1's defined, no records should be returned.

So my pseduo query is:

IF COUNT(SELECT * FROM Plans WHERE LOB=@LOB) = 0 THEN (SELECT * FROM Plans WHERE LOB=-1) ELSE (SELECT * FROM Plans WHERE LOB=@LOB)

Is there a way to do this in a single query?





kpg

---

SELECT *
FROM table
WHERE LOB = @YourValue
UNION ALL
SELECT *
FROM table
WHERE LOB = -1
AND NOT EXISTS (SELECT 1 FROM table
WHERE LOB = @YourValue
)