• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

Subquery and results in string format in mysql?

i have 3 tables :users,message,receiver
-----------------------------------------------------------------------------------
users
user_id,name
-----------------------------------------------------------------------------------
messages
message_id,user_id,message
-----------------------------------------------------------------------------------
receiver
message_id,sender_id,receiver_id
-----------------------------------------------------------------------------------

i have sent_messages.php

i have to show results in below

comma_seperated_names(to whom i send message)                                                  message
---------------------                                                                                                      -----------------
Adam,Joe,Mary,John                                                                                                         Hello

How can i do only one query

my query = select (select name from users u,receiver r where u.user_id=r.receiver_id and r.message_id=m.message_id) as comma_separated_names,m.message from messages m where m.user_id='10'

subquery returns more than 1 result , i want to subquery result be in one result and comma separated
0
phparmy
Asked:
phparmy
  • 3
  • 2
1 Solution
 
UmeshMySQL Principle Technical Support EngineerCommented:
Try this...


select GROUP_CONCAT(DISTINCT name SEPARATOR ',')  from users u,receiver r where u.user_id=r.receiver_id and r.message_id=m.message_id) as comma_separated_names,m.message from messages m where m.user_id='10'

Open in new window

0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Opps.... Pls try this..
select (select GROUP_CONCAT(DISTINCT name SEPARATOR ',') from users u,receiver r where u.user_id=r.receiver_id and r.message_id=m.message_id) as comma_separated_names,m.message from messages m where m.user_id='10'

Open in new window

0
 
phparmyAuthor Commented:
you have to remove DISTINCT then it works thanks.
0
 
phparmyAuthor Commented:
You have to remove distinct
0
 
UmeshMySQL Principle Technical Support EngineerCommented:

Thanks for the points!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now