Variable, multiple phone numbers for each user

Hello,
I’ve searched high and low and can’t find a solution… I’d be grateful if you could help or provide a pointer. Thanks in anticipation.

I have Table A, Users:


UserID FirstName LastName
1      Fred      Bloggs
2      John      Doe
3      Sam       Brown
4      Sue       Smith

Table B, Telephones


Tel_ID UserID Number Deleted
1      1      123    0
2      1      456    0
3      1      789    0
4      2      111    0
5      2      222    0
6      3      333    0
7      3      444    1

I need to structure the query so that I get:


UserID FirstName LastName Phone1 Phone2 Phone3
1      Fred      Bloggs   123    456    789
2      John      Doe      111    222    null
3      Sam       Brown    333    null   null
4      Sue       Smith    null   null   null

In other words, each user can have as many telephone numbers as necessary, and (unless they’ve been marked as deleted; see Tel_ID 7) they should all be listed in a single row along with the person’s name… The output is therefore “dynamic”: if, in the case shown, UserID 1 were to have a new telephone number (in table B), an extra column (Phone4) would be generated in the output. I hope that I’ve made the concept clear.

Thank you for your time.

you didn’t mention which database you’re using

if it’s mysql, the simplest approach is the GROUP_CONCAT function

if it’s sql server, you could use PIVOT

Thanks for the reply. I’m using MySQL (sorry for not mentioning that). I had actually tried your suggestion (before posting here), but apparently my syntax is on holiday… Could you please provide a more explicit hint? Many thanks.

SELECT u.UserID 
     , u.FirstName 
     , u.LastName
     , GROUP_CONCAT(t.Number) AS phones
  FROM Users AS u
INNER
  JOIN Telephones AS t
    ON t.UserID = u.UserID  
   AND t.Deleted = 0
GROUP
    BY u.UserID

Thanks again. Your reply has the correct syntax – of course! :slight_smile: I have your book by the way :slight_smile:
However, it doesn’t yield the output that I’m looking for: only one phone “field” is provided, comma-separated; what I had in mind was a separate phone “field” for each existing (and valid) phone associated with a user… Any ideas? Many thanks.

yes, my idea is that if you want to display separate columns for each phone number, you can de-serialize the comma-separated numbers a lot more easily in your application code than doing it with sql

doing it with sql means hard-coding the maximum number of columns you expect (which, of course, will break if there are more numbers) and the sql will be insanely complex and inefficient

that’s my idea :slight_smile:

Thanks again. Yes, I can easily explode the comma-separated phones in php, but that’s exactly what I was trying to avoid… For some reason, I was under the impression that I could do it directly in mysql WITHOUT hardcoding the total number of phones, in some elegant and simple way – dynamically spitting out ‘phone_1’ to ‘phone_i’… I’m almost sure that I saw this being done in some application or other, but I can’t for the life of me remember where…

Bypassing php (or any other code) has the advantage that the output of the query can be more easily made available as an Excel download, for example, in case it’s required by the end user. This is my main motivation, anyway – if the same thing can be done in a different way, I’d appreciate any pointers.

And if anyone else has a suggestion to achieve my original request, that would be much appreciated too – even if only for intellectual curiosity… :slight_smile:

Many thanks.

nope, sorry

this is ugly, complex, and inefficient, but you can have it if you want –

SELECT u.UserID 
     , u.FirstName 
     , u.LastName
     , t1.Number AS Phone1
     , t2.Number AS Phone2
     , t3.Number AS Phone3
  FROM Users AS u
LEFT OUTER
  JOIN Telephones AS t1
    ON t1.UserID = u.UserID  
   AND t1.Deleted = 0
   AND 0 =
       ( SELECT COUNT(*)
           FROM Telephones
          WHERE UserID = t1.UserID 
            AND Deleted = 0
            AND Tel_ID < t1.Tel_ID )
LEFT OUTER
  JOIN Telephones AS t2
    ON t2.UserID = u.UserID  
   AND t2.Deleted = 0
   AND 1 =
       ( SELECT COUNT(*)
           FROM Telephones
          WHERE UserID = t2.UserID 
            AND Deleted = 0
            AND Tel_ID < t2.Tel_ID )
LEFT OUTER
  JOIN Telephones AS t3
    ON t3.UserID = u.UserID  
   AND t3.Deleted = 0
   AND 2 =
       ( SELECT COUNT(*)
           FROM Telephones
          WHERE UserID = t3.UserID 
            AND Deleted = 0
            AND Tel_ID < t3.Tel_ID )