jeudi 23 juin 2016

Concat multiple results with left join not working

In my project I have a user table with regular information such as name, age, and the phone numbers are listed on another table with a relationship table to connect them.

Something like this.

TBL_User (name, dateofbirth, mail)
TBL_PhoneType (id, phonetype) //like, cellphone, home phone, etc.
TBL_PhoneNumber (id, phonetype, phonenuber) //lists muliple values for each user
TBL_PhoneRelation (userid, pnid)

I'm trying to make a selection to return the user information and a CONCAT version of the phone numbers, but the problem is the result that I get.

My first try is the GROUP_CONCAT, something like

SELECT us.name, us.dateofbirth, GROUP_CONCAT(' ', pt.phonetype, ' ', p.phonenumber) 
FROM TBL_User AS us 
LEFT JOIN TBL_PhoneRelation AS pr ON pr.userid = us.id 
LEFT JOIN TBL_PhoneNumber AS p ON p.id = pr.pnid

The problem is that I get only one row and not all the values from the database and a regular CONCAT show only one phone number, and sub selection gives a error because I have more than 1 row in my result.

I'm trying something like this

User name | Phone number                             | E-mail
Adrian    | Cellphone 11..., Home phone 22...        | adrian...
Suzan     | Cellphone 32..., Commercial phone 44...  | sue...

Aucun commentaire:

Enregistrer un commentaire