• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • 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
 
UmeshCommented:
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
 
UmeshCommented:
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
 
UmeshCommented:

Thanks for the points!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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