SQL join one row from a table to several rows from another one-CodePudding

I have a users table and a pic_urls table, and I want to extract data from several users. Each user may have several rows in the pic_urls table. This is the SQL I have so far:

    SELECT
      users.id,
      users.firstname,
      users.lastname,
      pic_urls.url
    FROM users
    JOIN pic_urls
    ON users.id = pic_urls.user_id
    WHERE users.id != ?

So far I get all the info I want from the users table, but only the last row of pic_urls for each user. My question is, how to group several rows of the pic_urls table (a user usually have several pics) into an array or something?

CodePudding user response：

One option uses a correlated subquery:

select u.id, u.firstname, u.lastname,
    ( 
        select json_arrayagg(pu.url)
        from pic_urls pu
        where pu.user_id = u.id
    ) all_urls
from users u

This aggregates all pic urls in a json array on each user row. Other aggregate options are available, such as group_concat() for string aggregation.