0

Hi everyone I'm facing a challenge, so I'm joining two tables, my course table & my videos table but the query below gives me duplicates on course id of which l want distinct rows eg so that l can only pull one video posted per course as my preview video.

SELECT DISTINCT * FROM `course` a
LEFT JOIN `media_center` b
ON a.name = b.course 
ORDER BY b.created_at DESC

the below picture is my result but if you check the first id column that's for course there is duplicate id eg 4, 4 then 3, l just want to have distinct especially on that column

enter image description here

  • 1
    They're not duplicates because they have differences in other columns. – Barmar Aug 01 '21 at 22:35
  • 1
    They have different video_name and description. – Barmar Aug 01 '21 at 22:36
  • okay so how can l only select at least one video or last video per that course id which is the first id on the picture, l just want to try to have 2 rows in the case with 4, 3 id values –  Aug 01 '21 at 22:38
  • As @Barmar has already pointed out, you do retrieve distinct records. If you want just 1 record per group, you need to decide which one to keep from the multiple records using either aggregation ( group by + min/max/any_value functions) or using some kind of ranking with filtering. All of these have solutions here on SO already. – Shadow Aug 01 '21 at 22:40
  • I would also add that if you have a numeric surrogate key in a table, then you usually use that as the foreign key as opposed to a long varchar field. Otherwise there is little point in having a surrogate key... – Shadow Aug 01 '21 at 22:44

0 Answers0