This is a query that is supposed to get the user's information, their project's information, and a group_concat of all the image paths that such project is associated to. To add to this, I am only getting the information mentioned from the people the user is following.
This is, however, only retunring one row.
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
TO COMPARE: The following query WORKS in the sense that in the loop it gives all of the user's information and the projects information related to such 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 just returns me one row and I do not understand why.
Can someone help me please? Thank you. If my question was not clear enough, I will elaborate.
If this helps, here's an SQL FIDDLE. http://www.sqlfiddle.com/#!2/867f6/2 I had to shorten my schema a lot. Try both queries to above to see the problem.
Show activity on this post. I'm using GROUP_CONCAT() in a MySQL query to convert multiple rows into a single string. However, the maximum length of the result of this function is 1024 characters.
The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value. Otherwise, it returns NULL.
The difference here is while CONCAT is used to combine values across columns, GROUP_CONCAT gives you the capability to combine values across rows. It's also important to note that both GROUP_CONCAT and CONCAT can be combined to return desired results.
When I try to use group_concat it just returns me one row and I do not understand why.
Because you have not used the GROUP BY
clause in your query. When using aggregate functions like GROUP_CONCAT
you need to tell the database about the column using which you want your data to be combined.
Currently your query is grouping all records and giving 1 record in the output.
If you add GROUP BY users.userid
in the query then the records will be grouped by unique userid's. I updated your fiddle and it now gives 2 records: http://www.sqlfiddle.com/#!2/867f6/18
Please note: In standard SQL queries, columns listed in the GROUP BY clause should match the column in the SELECT clause (except the aggregate functions).
Just use 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With