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

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

CodePudding user response：

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

CodePudding user response：

Their is no way to get your desired result given the data (and design) of the IMG table.

If your intention is that each blog will have one and only one img then either delete the eroneous records (ie ib_blog,filename 1,pic3.jpg 1,pic4.jpg) or add a img_filename column to the blog table and get rid of the img table.