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