CODEWITHSUNDEEP
mysqlsqldatabase
ChainFuse
ChainFuse

Reputation: 827

Query using group_concat is returning only one row

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.

Upvotes: 20

Views: 12377

Answers (4)

Hardip
Hardip

Reputation: 360

select json_group_array(json_object('shipment',ShipmentNo,'CreatedDateTime',CreatedDateTime)) as oujyu_name_list from outscan where flage='2'

Result : [{"CreatedDateTime":"2022-10-10 16:42:06","shipment":"456"},{"CreatedDateTime":"2022-10-10 16:42:06","shipment":"456457"},{"CreatedDateTime":"2022-10-10 16:42:06","shipment":"dfgdg"},{"CreatedDateTime":"2022-10-10 16:42:06","shipment":"4564fd"},{"CreatedDateTime":"2022-10-10 16:42:06","shipment":"456756nvjf"},{"CreatedDateTime":"2022-10-10 16:42:06","shipment":"457"},{"CreatedDateTime":"2022-10-10 16:42:06","shipment":"dfgdfg"},{"CreatedDateTime":"2022-10-10 16:42:06","shipment":"dfgdg"},{"CreatedDateTime":"2022-10-10 16:42:06","shipment":"dhf"}]

Upvotes: 0

ASammour
ASammour

Reputation: 1009

It's because you didn't use group by clause in the query. So the DBMS will group_concat all rows in one row. Because there is group by project_id for example.

Upvotes: 0

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

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).

Upvotes: 53

G one
G one

Reputation: 2729

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

Upvotes: 7

Related Questions