Sql Query Help

Question

Sql Query Help

Keejli69

Ten Centuries

Points: 1307
More actions
November 13, 2013 at 2:38 pm

#280197

I have query where i am using case statement and having issues. A person could select two options and if they select two options i do not want both output instead just THEN 'TWO ITEMS SELECTED' and when only only selection picked then output the corresponding result.
SELECT ID,
LAST_NAME,
FIRST_NAME,
CASE WHEN EXISTS (SELECT count(SELECTION) FROM ITEMS) >= 2 THEN 'Two Items selected'
WHEN SELECTON = -1 THEN 'A'
WHEN SELECTON = -2 THEN 'B'
WHEN SELECTON = -3 THEN 'C'
WHEN SELECTON = -4 THEN 'D'
WHEN SELECTON = -5 THEN 'E'
END AS item_selected
output would be like:
last_name first_name item_selected
------------ ------------- ----------------------
doe john Two Items selected
Not like:
last_name first_name item_selected
------------ ------------- ------------------
doe john a
doe john b

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

There's something wrong with your query. You're comparing a boolean value (EXISTS()) against an integer value.

To get better help, please post DDL for tables involved and sample data in the way of INSERT INTO statements. For information on how to do this, check the article linked in my signature.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101200 More actions · Answer 2

Something like below. If the LAST_NAME and FIRST_NAME are in the same table (shouldn't be, from a design standpoint), then you can remove the outer query and join and uncomment the LAST_NAME and FIRST_NAME references in the inner query:

SELECT

i_sel.ID, n.LAST_NAME, n.FIRST_NAME, i_sel.item_selected

FROM (

SELECT i.ID,

--LAST_NAME,

--FIRST_NAME,

CASE WHEN COUNT(i.SELECTION) >= 2 THEN 'Two Items selected'

WHEN MAX(i.SELECTION) = -1 THEN 'A'

WHEN MAX(i.SELECTION) = -2 THEN 'B'

WHEN MAX(i.SELECTION) = -3 THEN 'C'

WHEN MAX(i.SELECTION) = -4 THEN 'D'

WHEN MAX(i.SELECTION) = -5 THEN 'E'

END AS item_selected

FROM ITEMS i

GROUP BY i.ID --, LAST_NAME, FIRST_NAME

) AS i_sel

INNER JOIN NAMES n ON

n.ID = i.ID

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".