Early to development

Question

Early to development

mcmu4256

Valued Member

Points: 59
More actions
December 21, 2017 at 2:32 pm

#330635

I have table "Account"
--Account-- --ShareType--
0123456 1
0123456 2
2222222 2
2222222 2
In a query, how do I return a 'Yes' if the Accounthas ShareType 1 but still return a 'No' for the Accounts that don't have a ShareType of 1?
I am sure this is super simple, but it has been a long week.
Thanks for all any help!

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You probably want to use EXISTS and/or NOT EXISTS

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mcmu4256 Valued Member Points: 59 More actions · Answer 2

Thank you, maybe I'm not explaining my issue clearly.
I want to get all of the Accounts, regardless of whether they have a 1 or 2 in the ShareType column, but I don't want duplicates of the Accounts.
If the Account has a 1 and a 2 in the ShareType, I just want to see yes, but if the Account has a 2 and a 3 in the ShareType, I just want to see that it doesn't have a 1, but I still want to see the Account Number.
Does that make sense?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Yes.
You probably want to use EXISTS and/or NOT EXISTS. Since you have to check multiple rows for each row, doing this needs a subquery. One used with either EXISTSor NOT EXISTS (or both)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Something like this may work:
with base as ( select rn = row_number() over (partition by Account order by Sharetype asc) , Account , ShareType from Account ) select Account , ShareType1 = case ShareType when 1 then 'Yes' else 'No' end from base where rn = 1;

drew.allen SSC Guru Points: 76944 More actions · Answer 5

SELECT
Account,
ShareType1 = MAX(CASE WHEN ShareType = 1 THEN 'Y' ELSE 'N' END)
FROM #Account
GROUP BY Account

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

mcmu4256 Valued Member Points: 59 More actions · Answer 6

GilaMonster - Thursday, December 21, 2017 2:35 PM
You probably want to use EXISTS and/or NOT EXISTS

Perfect! Thanks for the help!