How to find the family for contact associations

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to find the family for contact associations

Author

Topic

smisich
Starting Member

5 Posts

Posted - 2011-06-17 : 15:47:38

I need to group contacts that are related into families or related members.

I have a table that stores associations
eg
Assoc1 Name1 Assoc2 Name2
Business xyz ltd Business Owner Sam Smith
RelatedCompany abc ltd Business xyz ltd
Father Sam Smith Son Tom Smith
Wife Sally Smith Husband Sam Smith
Trust Smith Trust Trustee Sam Smith
Business Kodak Business Owner Cam Rah.
Trust Kodak Trust Trustee Les Smith
I need a query that lists all contacts into families.
Note there is no logic as to whether the contact is stored under assoc1 or assoc2 ie assoc1 is not necessarily the primary contact.

eg
The list would be
family 1
xyz ltd
abc ltd
Sam Smith
Sally Smith
Smith Trust
Tom Smith

family 2
Kodak
Kodak Trust
Cam Rah
Les Smith

etc

I am try to sum the sales of all related contacts.
How can I achieve this?

super_lucky_dog
Starting Member

7 Posts

Posted - 2011-06-23 : 02:24:02

hi there is my answers.
I added data to provide relationship between trust and business owner.

--ddl definition
CREATE table associations
(
Assoc1 nvarchar(40) NOT NULL,
Name1 nvarchar(40) NOT NULL,
Assoc2 nvarchar(40) NOT NULL,
Name2 nvarchar(40) NOT NULL)

truncate table associations;
--data you provide
insert into associations values('Business','xyz ltd','Business Owner','Sam Smith');
insert into associations values('RelatedCompany','abc ltd','Business','xyz ltd');
insert into associations values('Father','Sam Smith','Son','Tom Smith');
insert into associations values('Wife','Sally Smith','Husband','Sam Smith');
insert into associations values('Trust','Smith Trust','Trustee','Sam Smith');
insert into associations values('Business','Kodak','Business Owner','Cam Rah');
insert into associations values('Trust','Kodak Trust','Trustee','Les Smith');

--data you did not provide but i needed
insert into associations values('Trust Owner','Sam Smith','Trust','Smith Trust');
insert into associations values('Trust Owner','Cam Rah','Trust','Kodak Trust');

--my solution
select
RelatedCompany.RelatedCompanyName,
Business.BusinessName,
Business.BusinessOwnerName,
Business.SonName,
Business.WifeName,
Business.TrustName,
Business.TrusteeName
from
(
select Name1 RelatedCompanyName,Name2 BusinessName
from associations
where Assoc1='RelatedCompany'
)RelatedCompany
right outer join
(
select Business.Name1 BusinessName
,Business.Name2 BusinessOwnerName
--,Father.Name1 BusinessOwnerName
,Father.Name2 SonName
--,Wife.Name2 BusinessOwnerName
,Wife.Name1 WifeName
--,TrustOwner.Name1 BusinessOwnerName
,TrustOwner.Name2 TrustName
--,Trust.Name1 TrustName
,Trust.Name2 TrusteeName
from associations Business
left outer join associations Father
on Business.Name2 = Father.Name1
and Father.Assoc1='Father'
left outer join associations Wife
on Business.Name2 = Wife.Name2
and Wife.Assoc1='Wife'
left outer join associations TrustOwner
on Business.Name2 = TrustOwner.Name1
and TrustOwner.Assoc1='Trust Owner'
left outer join associations Trust
on TrustOwner.Name2 = Trust.Name1
and Trust.Assoc1='Trust'
where Business.Assoc1='Business'
)Business
on RelatedCompany.BusinessName=Business.BusinessName

v-jinxli

super_lucky_dog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources