One-to-many join using separate fields for values?
-
Hi everyone
I am fairly new to sql but trying to learn fast. I must say, the threads I have read so far have been a great help but I still need help with a particular problem and cannot seem to find a thread that has the answer (this may be because I am asking the wrong question of course!)
I have 2 tables called 'Contact' and 'Communications'. Communications holds details of the phone numbers etc for contacts in the Contact table. The tables are as follows...
Contact
Contact_Ref (PK) Name
1 Joe Bloggs
2 Jane Bloggs
3 Mary Bloggs
etc...
Communications
Unique_Number (PK) Location Method Number Contact_Ref (FK)
1 Home Telephone 01234 567890 1
2 Work Telephone 01345 678901 1
3 Home Telephone 01456 789012 2
4 Work Telephone 01567 890123 2
5 Mobile Telephone 07678 901234 2
etc...
What I am trying to acheive is a single row of data (that I can select into a new table) for each contact that has the Home Phone, Work Phone and Mobile Phone as individual fields.
Basically this...
Contact_Ref (PK) Name Home_Phone Work_Phone Mobile_Phone
1 Joe Bloggs 01234 567890 01345 678901 NULL
2 Jane Bloggs 01456 789012 01567 890123 07678 901234
3 Mary Bloggs NULL NULL NULL
The very basic code I have been working with so far is this...
select c.contact_ref,
c.[name],
(select co.number where co.location='Home' and co.method='Telephone') as Home_Phone,
(select co.number where co.location='Work' and co.method='Telephone') as Work_Phone,
(select co.number where co.location='Personal' and co.method='Mobile') as Mobile_Phone
from contact as c
left outer join communications as co
on c.contact_ref=co.contact_ref
What I end up with is this...
Contact_Ref (PK) Name Home_Phone Work_Phone Mobile_Phone
1 Joe Bloggs 01234 567890 NULL NULL
1 Joe Bloggs NULL 01345 678901 NULL
2 Jane Bloggs 01456 789012 NULL NULL
2 Jane Bloggs NULL 01567 890123 NULL
2 Jane Bloggs NULL NULL 07878 901234
3 Mary Bloggs NULL NULL NULL
I have tried all sorts of 'group by' options and a few other things I know but nothing seems to do the trick so I'm a little stuck can anyone help?
Many thanks,
Peter Smith
(SQL Newbie)
-
Peter,
You're already along the right lines. Try this:
select c.contact_ref,
c.[name],
(select co.number from Communication co where co.location='Home' and co.contact_ref=c.contact_ref) as Home_Phone,
(select co.number from Communication co where co.location='Work' and co.contact_ref=c.contact_ref) as Work_Phone,
(select co.number from Communication co where co.location='Mobile' and co.contact_ref=c.contact_ref) as Mobile_Phone
from contact as c
-
A couple of other possibilities
select c.contact_ref,c.[name],
max(case when co.location='Home' and co.method='Telephone' then co.number end) as Home_Phone,
max(case when co.location='Work' and co.method='Telephone' then co.number end) as Work_Phone,
max(case when co.location='Personal' and co.method='Mobile' then co.number end) as Mobile_Phone
from contact as c
left outer join communications as co
on c.contact_ref=co.contact_ref
group by c.contact_ref,c.[name]
select c.contact_ref,c.[name],
co1.number as Home_Phone,
co2.number as Work_Phone,
co3.number as Mobile_Phone
from contact as c
left outer join communications as co1 on c.contact_ref=co1.contact_ref and co1.location='Home' and co1.method='Telephone'
left outer join communications as co2 on c.contact_ref=co2.contact_ref and co2.location='Work' and co2.method='Telephone'
left outer join communications as co3 on c.contact_ref=co3.contact_ref and co3.location='Personal' and co3.method='Mobile'
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thank you Karl and Mark for your input, that's brilliant.
I hope I may be of some help to some of you in the future but until then, I know where to come when I'm stuck.
Top class.
-
Good suggestions Mark. The first one (using the Max method) is the option to go with as that requires a third the number of reads as my method, which is equivalent to the second method you suggested.
My method ends up with a Scan count of 9 and 9 logical reads Table 'Communication'.
The MAX method uses 1 scan count and 3 logical reads.
Neat, I'll remember that trick.
Mark (4/2/2008)
A couple of other possibilitiesselect c.contact_ref,c.[name],
max(case when co.location='Home' and co.method='Telephone' then co.number end) as Home_Phone,
max(case when co.location='Work' and co.method='Telephone' then co.number end) as Work_Phone,
max(case when co.location='Personal' and co.method='Mobile' then co.number end) as Mobile_Phone
from contact as c
left outer join communications as co
on c.contact_ref=co.contact_ref
group by c.contact_ref,c.[name]
select c.contact_ref,c.[name],
co1.number as Home_Phone,
co2.number as Work_Phone,
co3.number as Mobile_Phone
from contact as c
left outer join communications as co1 on c.contact_ref=co1.contact_ref and co1.location='Home' and co1.method='Telephone'
left outer join communications as co2 on c.contact_ref=co2.contact_ref and co2.location='Work' and co2.method='Telephone'
left outer join communications as co3 on c.contact_ref=co3.contact_ref and co3.location='Personal' and co3.method='Mobile'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply