HELP WITH A CASE STATEMENT

Question

HELP WITH A CASE STATEMENT

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
November 13, 2015 at 3:16 am

#313122

Hi,
I have three fields in my query - ContactGroup, Contact and RelationshipID
All 10 entries are part of the same ContactGroup.
ContactGroup Contact RelationshipId
ABL001 ABL0011 Lead Tenant
ABL001 ABL00110 Son
ABL001 ABL0012 Son
ABL001 ABL0013Son
ABL001 ABL0014Son
ABL001 ABL0015Son
ABL001 ABL0016Daughter
ABL001 ABL0017Son
ABL001 ABL0018Daughter
ABL001ABL0019Daughter
I want to create another field, which is the RelatedContactId. So if RelationshipId = "Lead Tenant" leave the RelatedContactId blank, otherwise for the other members of the group the RelatedContactId will be the Contact of the LeadTenant - so for 9 entries the result for RelatedContactId would be ABL0011
Many Thanks

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

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

joeroshan SSChampion Points: 10377 More actions · Answer 1

TSQL Tryer (11/13/2015)
Hi,
I have three fields in my query - ContactGroup, Contact and RelationshipID
All 10 entries are part of the same ContactGroup.
ContactGroup Contact RelationshipId
ABL001 ABL0011 Lead Tenant
ABL001 ABL00110 Son
ABL001 ABL0012 Son
ABL001 ABL0013Son
ABL001 ABL0014Son
ABL001 ABL0015Son
ABL001 ABL0016Daughter
ABL001 ABL0017Son
ABL001 ABL0018Daughter
ABL001ABL0019Daughter
I want to create another field, which is the RelatedContactId. So if RelationshipId = "Lead Tenant" leave the RelatedContactId blank, otherwise for the other members of the group the RelatedContactId will be the Contact of the LeadTenant - so for 9 entries the result for RelatedContactId would be ABL0011

I think case wont work here unless you use a correlated subquery. You can better write a self join on the table to achieve this.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 2

Thanks for your reply but sorry I don't know how to achieve that.

Phil Parkin SSC Guru Points: 246993 More actions · Answer 3

If you take the time to post the data in consumable form (see the link in my signature), I will show you how.

A correlated subquery is not required in this case.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Neil Burton SSC-Insane Points: 23014 More actions · Answer 4

create table #relate

(

ContactGroup char(6),Contact varchar(20),RelationshipId varchar(20)

)

insert into #relate values

('ABL001' ,'ABL0011' ,'Lead Tenant')

,('ABL001' ,'ABL00110' ,'Son')

,('ABL001' ,'ABL0012' ,'Son')

,('ABL001' ,'ABL0013' ,'Son')

,('ABL001' ,'ABL0014' ,'Son')

,('ABL001' ,'ABL0015' ,'Son')

,('ABL001' ,'ABL0016' ,'Daughter')

,('ABL001' ,'ABL0017' ,'Son')

,('ABL001' ,'ABL0018' ,'Daughter')

,('ABL001' ,'ABL0019' ,'Daughter')

,('ABL002' ,'ABL0020' ,'Lead Tenant')

,('ABL002' ,'ABL0021' ,'Daughter')

,('ABL002' ,'ABL0022' ,'Daughter')

select

r1.ContactGroup

,r1.Contact

,r1.RelationshipId

,RelatedContactid = case when r1.relationshipid = 'Lead Tenant' then NULL else r2.contact end

from #relate r1

join #relate r2 on r2.contactgroup= r1.contactgroup

and r2.relationshipid= 'Lead Tenant'

drop table #relate

This will do it but it's almost certainly not the best way.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 5

/****** Object: Table [dbo].[Contact] Script Date: 13/11/2015 11:38:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING OFF

GO

CREATE TABLE [dbo].[Contact](

[ContactId] [varchar](100) NULL,

[ContactGroupRef] [varchar](100) NULL,

[RelationshipToLead] [varchar](100) NULL,

[id] [int] IDENTITY(1,1) NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

Dataset

INSERT INTO Contact (ContactGroupRef,ContactId,RelationshipToLead)

SELECT 'ABL001','ABL0011','Lead Tenant' UNION ALL

SELECT 'ABL001','ABL00110','Son' UNION ALL

SELECT 'ABL001','ABL0012','Son' UNION ALL

SELECT 'ABL001','ABL0013','Son' UNION ALL

SELECT 'ABL001','ABL0014','Son' UNION ALL

SELECT 'ABL001','ABL0015','Son' UNION ALL

SELECT 'ABL001','ABL0016','Daughter' UNION ALL

SELECT 'ABL001','ABL0017','Son' UNION ALL

SELECT 'ABL001','ABL0018','Daughter' UNION ALL

SELECT 'ABL001','ABL0019','Daughter'

Many Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Here's another way:

SELECT

co.ContactGroupRef,

co.ContactId,

co.RelationshipToLead,

[RelatedContactId] = a.ContactId

FROM #Contact co

OUTER APPLY (

SELECT ContactId

FROM #Contact ci

WHERE ci.ContactGroupRef = co.ContactGroupRef

AND ci.ContactId <> co.ContactId

AND ci.RelationshipToLead = 'Lead Tenant'

) a

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden