How to eliminate a duplicate row in SQL by picking one

Question

I have a DB where there are [Session] and [SessionAsset] tables.

SessionAsset has a ThumbnailUrl that I want to get.

There are two SessionAssets associated with a single Session.

What would be the best way to take just 1 of those ThumbnailUrls? If I join Session with SessionAsset on SessionId (primary key), then there will be duplicate rows.

Answer 1

Assuming that both records in SessionAssets have the same ThumbnailUrl, one solution would be to use a subquery that selects distinct SessionId/ThumbnailUrl tuples.

SELECT s.*, sa.ThumbnailUrl
FROM Session s
INNER JOIN ( -- or maybe LEFT JOIN ?
    SELECT DISTINCT SessionID, ThumbnailUrl FROM SessionAssets
) sa ON sa.SessionId = s.SessionID

---

If the ThumbnailUrls are different and you want to get just one of them, then you might want to use aggregation :

SELECT s.*, sa.ThumbnailUrl
FROM Session s
INNER JOIN (
    SELECT SessionID, MAX(ThumbnailUrl) ThumbnailUrl 
    FROM SessionAssets 
    GROUP BY SessionID
) sa ON sa.SessionId = s.SessionID

Answer 2

Join two tables through [SessionId], and use DISTINCT keyword to eliminate duplicated [ThumbnailUrl]s.

SELECT DISTINCT ThumbnailUrl
FROM SessionAsset sa
JOIN Session s on s.SessionId = sa.SessionId