Geek Answers Handbook

A query using group_concat returns only one row

This is a request that should receive information about the user, information about his project, and group_concat of all image paths to which such a project is connected. To add to this, I only get information from the people the user is following.

This, however, is only for retransmission of a single line.

SELECT users.first_name, users.last_name, users.user_id, projects.project_id, projects.project_name, projects.project_time, group_concat(images.image_path) FROM users, projects, images WHERE users.user_id = projects.user_id AND users.user_id IN (SELECT follow_user_2 FROM following WHERE follow_user_1 = 1) ORDER BY projects.project_id DESC

COMPARE: The next WORK request in the sense that in the cycle it provides all the information about the user and information about projects related to that user.

 SELECT users.first_name, users.last_name, users.user_id, projects.project_id, projects.project_name, projects.project_time FROM users, projects WHERE users.user_id = projects.user_id AND users.user_id IN (SELECT follow_user_2 FROM following WHERE follow_user_1 = 1) ORDER BY projects.project_id DESC

When I try to use group_concat, it returns me the string one , and I do not understand why.

Can someone help me? Thanks. If my question were not clear enough, I will clarify.

If that helps, here is SQL FIDDLE. http://www.sqlfiddle.com/#!2/867f6/2 I had to significantly reduce my scheme. Try both queries above to see the problem.

+10
source share
3 answers

When I try to use group_concat, it just returns me one line, and I don’t understand why.

Because you did not use the GROUP BY in your query. When using aggregate functions such as GROUP_CONCAT , you need to tell the database about the column in which you want to combine the data.

Currently, your request groups all the records and gives 1 record in the output.

If you add GROUP BY users.userid to the request, the entries will be grouped by a unique user ID. I updated your fiddle and now it gives 2 entries: http://www.sqlfiddle.com/#!2/867f6/18

Please note: in standard SQL queries, the columns listed in the GROUP BY clause must correspond to the column in the SELECT clause (except aggregate functions).

+32
source

Just use the group by clause in your_query

 SELECT users.first_name, users.last_name, users.user_id, projects.project_id, projects.project_name, projects.project_time, group_concat(images.image_path) FROM users, projects, images WHERE users.user_id = projects.user_id AND users.user_id IN (SELECT follow_user_2 FROM following WHERE follow_user_1 = 1) group by users.first_name ORDER BY projects.project_id DESC;

fiddle

+4
source

This is because you did not use group by in the request. Thus, the DBMS will group_concat all the lines in one line. Because there is group by project_id for example.

0
source

More articles:


All Articles