0

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
Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
Sandowl
  • 73
  • 1
  • 4
  • You can apply partition by. For simplification you can use with clause and then it will be easier to apply partition by. Something like : With a as ( your select statement), b as (select *, row_number() over ( partition by name, content, id_blog, filename order by filename desc ) as rn ) select * from b where rn = 1; – dowonderatwill Feb 15 '22 at 13:31

2 Answers2

0

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

Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
  • Thanks. It's works! I've tried this way but I got MySQL error: SELECT list is not in GROUP BY clause and contains nonaggregated column which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by I didn't know I should user Alias with function. – Sandowl Feb 15 '22 at 13:46
  • @Sandowl check the `ORDER BY` clause, I edited the answer. Try and let me know – Ergest Basha Feb 15 '22 at 13:54
  • There is no need to add MAX() function in ORDER BY. Everythings works fine. Thanks. – Sandowl Feb 15 '22 at 14:52
  • OK. Thanks for help. – Sandowl Feb 15 '22 at 15:30
0

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.

rabbit
  • 95
  • 7