CODEWITHSUNDEEP
sqlselectunioncase
Joshua
Joshua

Reputation: 107

Case when within a union?

Here's my code:

SELECT 'Add or remove a keyword' AS 'Keyword', '1' AS 'row number'
UNION
SELECT '---------------------', '2'
UNION
SELECT 
   CASE
   WHEN @list_item = 'duration'
   THEN (SELECT duration FROM durationTable)
   END
   CASE
   WHEN @list_item = 'location'
   THEN (SELECT location FROM locationTable)
   END
, '3'
ORDER BY 2

The trouble is, this sees the duration and location selects as subqueries, and subqueries are limited to only one result, and furthermore, I can't seem to figure out the correct syntax to bump my CASE up a level so the queries aren't subqueries.

Is there a way that I can keep the structure that the union / select allows me, while also changing up which table is queried for the last bit, and also retrieving the entire table column instead of one result?

Any tips? Thank you so much for taking a look!

Upvotes: 1

Views: 8702

Answers (3)

Abdul Rasheed
Abdul Rasheed

Reputation: 1

SELECT 'Add or remove a keyword' AS 'Keyword', '1' AS 'row number'
UNION
SELECT '---------------------', '2'
UNION
SELECT duration,3 FROM durationTable WHERE @list_item = 'duration'
UNION
SELECT location,3 FROM locationTable WHERE @list_item = 'location'

Upvotes: 0

JNK
JNK

Reputation: 65187

First, you should know this is a bad design. You are mixing up your presentation layer and your data layer and it will lead to problems down the road.

However, if you are absolutely committed to doing this, you can do it with an additional SELECT:

...
UNION

SELECT duration FROM durationTable, '3'
WHERE @list_item = 'duration'

UNION

SELECT location FROM locationTable, '3'
WHERE @list_item = 'location'
...

The non-matching query will return an empty result set and be ignored.

Upvotes: 2

PaulStock
PaulStock

Reputation: 11273

It's not pretty, but I think this should work:

SELECT 'Add or remove a keyword' AS 'Keyword', '1' AS 'row number'
UNION
SELECT '---------------------', '2'
UNION
SELECT duration, '3' FROM durationTable WHERE 1 = (SELECT CASE WHEN @list_item = 'duration' THEN 1 ELSE 0 END)
UNION
SELECT location, '3' FROM locationTable WHERE 1 = (SELECT CASE WHEN @list_item = 'location' THEN 1 ELSE 0 END)

Upvotes: 1

Related Questions