CODEWITHSUNDEEP
mysqlsqlsql-server
korben
korben

Reputation: 537

SQL INNER JOIN with DISTINCT rows?

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

Upvotes: 3

Views: 15820

Answers (3)

J Cooper
J Cooper

Reputation: 5008

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

Upvotes: 3

Aprillion
Aprillion

Reputation: 22340

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

Upvotes: 2

Vince Pergolizzi
Vince Pergolizzi

Reputation: 6594

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

Upvotes: 5

Related Questions