Displaying result of joining two MySQL tables without duplicates, using DISTINCT or GROUP BY

Question

I have two tables: BLOG table withe scheme (id_blog, name, content, date, building) and IMG table with scheme (id_img, filename, id_blog).

With query below, I've got result of LEFT JOIN tables BLOG and IMG and it's OK.

SELECT b.name, 
       b.content, 
       i.id_blog, 
       i.filename
FROM blog b
LEFT JOIN img i USING(id_blog)
WHERE building IN (2,3)
ORDER BY i.filename DESC

My query result:

Building A | Warehouse | 1 | pic3.jpg
Building A | Warehouse | 1 | pic4.jpg
Building A | Warehouse | 1 | pic6.jpg
Building B | Store     | 2 | pic7.jpg
Building B | Store     | 2 | pic9.jpg
Building B | Store     | 2 | pic8.jpg
Building C | School    | 3 | pic5.jpg

What should I do to get result without duplicates name, content, id_blog columns.

What I need is result below:

Building A | Warehouse | 1 | pic6.jpg
Building B | Store     | 2 | pic9.jpg
Building C | School    | 3 | pic5.jpg

Answer 1

Use:

SELECT b.name, 
       b.content, 
       i.id_blog, 
       max(i.filename)  as filename
FROM blog b
LEFT JOIN img i USING(id_blog)
WHERE building IN (2,3)
GROUP BY b.name,b.content,i.id_blog
ORDER BY filename DESC;

Demo