One Row from Multiple ( Phone Numbers)

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 2008 Forums

Transact-SQL (2008)

One Row from Multiple ( Phone Numbers)

Author

Topic

Morke
Starting Member

5 Posts

Posted - 2011-10-22 : 07:32:57

I am trying to join two tables where the person in the contacts table may have multiple phone numbers in the phonenumbers table,

Members:
me_id
me_fullname
....

PhoneNumbers
ph_id
ph_number
ph_type 1: Landline, 2 mobile, 3 SMS text phone

So rather than get
John Smith, 34343434 ,1
John Smith, 45454545, 3
John Smith, 67676767, 3

I would like to be able to return a row as':
John Smith, 67676767, 45454545, 67676767
Or
John Smith, Null, Null, 67676767 if there was only a sms text number.

I have spent a couple of hours along variations of the following without success.
select M.me_surname, P1.ph_number as PhoneNumber1, P2.ph_number as PhoneNumber2
FROM
members M
right join PhoneNos P1
on M.me_id=P1.ph_me_id
left outer join PhoneNos P2
on M.me_id=P2.ph_me_id
left outer join PhoneNos P3
on M.me_id=P3.ph_me_id
where me_id = 654322

This returns 27 rows of 3 * 3 * 3

Any assistance appreciated.

Morke

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-22 : 07:46:48

[code]
SELECT M.me_surname,
STUFF((SELECT ',' + CAST(ph_number AS varchar(20)) FROM PhoneNos WHERE ph_me_id=M.me_id ORDER BY ph_type FOR XML PATH('')),1,1,'') AS PhoneNumbers
FROM members M
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Morke
Starting Member

5 Posts

Posted - 2011-10-22 : 10:02:17

Hi,
Thank you very much for the quick response. However, the problem with your solution is that it does not allow me to determine which type of number is returned. For instance if John Smith only has a number tagged as 3 (sms_message) then although the number is returned there is no way of determining whether this is a SMS texting number or his mobile contact number..

Ideally, I would like to be able to return a row as':
John Smith, 67676767, 45454545, 67676767
Or in the event that no numbers existed for, say, the landline and the mobile, then
John Smith, Null, Null, 67676767.
Still I appreciate the effort.
Morke

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-22 : 11:49:41

[code]
;With CTE
AS
(
SELECT M.me_surname,
p.ph_type,
ph.ph_number
FROM members M
CROSS JOIN (SELECT DISTINCT ph_type FROM PhoneNumbers) p
LEFT JOIN PhoneNumbers ph
ON ph.ph_me_id = M.me_id
AND ph.ph_type = p.ph_type
)

SELECT me_surname,
STUFF((SELECT ',' + ISNULL(CAST(ph_number AS varchar(10),'Null') FROM CTE WHERE me_surname = c.me_surname FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT me_surname FROM CTE) c
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Morke
Starting Member

5 Posts

Posted - 2011-10-24 : 06:19:55

HI,
Getting the following errors:
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near 'c'.

I have created a workaround by reading each of the numbers into a dataaset and manipulating that in a .net application
Thanks for the assist
Morke

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 06:24:45

[code]
;With CTE
AS
(
SELECT M.me_surname,
p.ph_type,
ph.ph_number
FROM members M
CROSS JOIN (SELECT DISTINCT ph_type FROM PhoneNumbers) p
LEFT JOIN PhoneNumbers ph
ON ph.ph_me_id = M.me_id
AND ph.ph_type = p.ph_type
)

SELECT c.me_surname,
STUFF((SELECT ',' + ISNULL(CAST(ph_number AS varchar(10)),'Null') FROM CTE WHERE me_surname = c.me_surname FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT me_surname FROM CTE) c
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-10-24 : 08:45:05

[code]SELECT m.*,
w.Landline,
w.Mobile,
w.SMS
FROM dbo.Members AS m
INNER JOIN (
SELECT ph_me_id,
MAX(CASE WHEN ph_type = 1 THEN ph_number ELSE NULL END) AS Landline,
MAX(CASE WHEN ph_type = 2 THEN ph_number ELSE NULL END) AS Mobile,
MAX(CASE WHEN ph_type = 3 THEN ph_number ELSE NULL END) AS SMS
FROM dbo.PhoneNos
GROUP BY ph_me_id
) AS w ON w.ph_me_id = m.me_id[/code]

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 09:14:42

i thought the reqmnt was to get the numbers comma separated into a single column.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-10-24 : 09:36:54

It is a WELL KNOWN fact that Peter can read minds better than you Visakh.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 09:40:56

quote:
Originally posted by DonAtWork

It is a WELL KNOWN fact that Peter can read minds better than you Visakh.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

thats true

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources