Link 3 tables in an SQL query

Question

I’m in a quasi-deadly doubt I don’t know will it be possible to run with just one query. I have three tables in the bank where they relate to foreign keys so far everything well I am able to relate them. The problem becomes where the Table Images has no or several images linked to a post, only then I want to return the data of the post, with the user who posted and the images if there is, that part of the image that is intriguing me, I want to get only the URLS of the images but instead it duplicates the post and returns me along with the different urls. I am running this query below:

SELECT P.*, U.fistname, U.lastname, U.username, PI.url
FROM posts as P
INNER JOIN user as U ON P.User_id = U.id
LEFT JOIN post_has_image as PI ON PI.Posts_id = P.id
WHERE U.id IN (SELECT User_id1 FROM followers WHERE User_id = 1);

But the result is:

I would like the following result:

2 message first2 2 Harry Potter harrypotter url url url(since there are 3 urls in the table image)

3 Massage first3 2 Harry Potter harrypotter null

4 message first4 3 Hermione Granger Hermione null

If the table mount is wrong or there is some way to have this result in a single query, I know that running two queries with you returns but I wonder if there really is a solution to run in just one query.

Thanks in advance.

Answer 1

In this specific case, you can use a sub-select, since it is selecting only one column from the foreign table, which avoids you placing all the other columns in the group by. If you do not want a sub-select, you will have to write the complete query including taking the *

That said, just group several lines in one, in this case using the function GROUP_CONCAT

SELECT 
    P.*, 
    U.fistname, 
    U.lastname, 
    U.username, 
    (Select  GROUP_CONCAT(PI.url) from post_has_image as PI WHERE PI.Posts_id = P.id) as imgs
FROM posts as P
INNER JOIN user as U ON P.User_id = U.id
WHERE U.id IN (SELECT User_id1 FROM followers WHERE User_id = 1);

by default, all URL will be separated by comma, if you want, you can enter another tab.