SQL INNER JOIN with DISTINCT rows?
12,144
Solution 1
Here's one approach (sql server only):
SELECT collections.title, collections.uniqueID, uploads.uniqueID as thumb
FROM collections
CROSS APPLY (
SELECT TOP 1 *
FROM uploads
WHERE collections.uniqueID = uploads.uploadGroup
ORDER BY uploads.uniqueID -- not required, can be changed to any column you want
) uploads
Solution 2
If you need a simple distinct list from the uploads table then you can do it like this:
SELECT collections.title, collections.uniqueID, uploads.uniqueID as thumb
FROM collections
INNER JOIN (SELECT DISTINCT uniqueID FROM uploads) uploads ON collections.uniqueID = uploads.uploadGroup
WHERE collections.owner = @owner
Solution 3
SELECT collections.title, collections.uniqueID, min(uploads.uniqueID) as thumb
FROM collections
JOIN uploads ON collections.uniqueID = uploads.uploadGroup
WHERE collections.owner = @owner
GROUP BY collections.title, collections.uniqueID
Author by
korben
Updated on June 09, 2022Comments
-
korben almost 2 years
SELECT collections.title, collections.uniqueID, uploads.uniqueID as thumb FROM collections INNER JOIN uploads ON collections.uniqueID = uploads.uploadGroup WHERE collections.owner = @owner
in this instance if I have 3 rows in "uploads" that have the matching uniqueid/uploadgroup (as in 3 uploads in a collection) this gives me back 3 rows.
what I'm looking for is ONE row for each DISTINCT COLLECTIONS.UNIQUEID - is this possible?
so when collections has
1 | title | idhere
and uploads has
1 | uniqueID1 | idhere 2 | uniqueID2 | idhere 3 | uniqueID3 | idhere
I'm currently returning
1 | title | uniqueID1 2 | title | uniqueID2 3 | title | uniqueID3
when all I want to return is
1 | title | uniqueID1