Get Distinct rows from a result of JOIN in SQL Server

Question

I have two tables one I use to store Album_Name and other table I use to store Album_Photos

I want to write a query so that it will get me following details

Album_Name    Album_ID     Album_Date     ImageSmall
Album One     1            2013-08-02      100.jpg
Album Two     2            2013-09-09      55.jpg

I want details album details from Album_Name table and first image which I wasnt to assign to album from Album_Photo table

I tried JOINS which didn't work for then I create a view will following SQL this doesn't work

SELECT 
   a.Album_Name AS Album_Name
   , a.Album_Date AS Album_Date
   , a.Page_ID AS PageID
   , p.Image_ID AS Image_ID
   , p.Image_Small AS Image_Small 
FROM 
   Album_Name a
LEFT OUTER JOIN 
   Album_Photos p ON a.Album_ID = p.Album_ID

I tried DISTINCT Album_Name with view it get me the same row as the above statement

SELECT 
    DISTINCT [Album_Name], Album_Date, Page_ID, Image_Small  
FROM 
    vw_AlbumName_AlbumPhotos 
WHERE 
    Page_ID = 3

Sample Data Album_Name & Album_Photos table

Album_ID    Album_Name  Album_Date  Page_ID
1   Album One   2013-08-02      3
2   Album Two   2013-09-09      3
3   Album Three 2013-09-10      9

Image_ID    Page_ID Album_ID    ImageSmall
1       0       1   100.jpg
2       0       1   21.jpg
3       0       1   36.jpg
4       0       1   44.jpg
5       0       2   55.jpg
6       0       2   66.jpg
7       0       3   10.jpg

Any help is appreciated.

Which photo record do you want to show - the one with the smallest `Image_ID`? — CompanyDroneFromSector7G, Sep 10 '13 at 11:38
Yes, Image_ID but i want to select always album icon as first photo that was created for this album — Learning, Sep 10 '13 at 11:42

Gordon Linoff · Accepted Answer · 2013-09-10T11:53:07.203

5

You are getting duplicate because there are multiple photos per album. To get one, use row_number():

SELECT Album_Name AS Album_Name, a.Album_Date AS Album_Date, a.Page_ID AS PageID,
       p.Image_ID AS Image_ID, p.Image_Small AS Image_Small 
FROM Album_Name a left outer JOIN
     (select p.*, row_number() over (partition by Album_Id order by Image_ID) as seqnum
      from Album_Photos p 
     ) p
    ON a.Album_ID = p.Album_ID and seqnum = 1;

edited Sep 10 '13 at 11:53

answered Sep 10 '13 at 11:38

Gordon Linoff

1,242,037
58
646
786

1

@KnowledgeSeeker . . . I see that. I changed it to `Image_Id`. The question is how you define "first" for the images. – Gordon Linoff Sep 10 '13 at 11:53
First should be the first photo that we added to that album. – Learning Sep 10 '13 at 11:55
It works & get me the unique records. I tried adding `WHERE` clause which gave me error how can i add ` WHERE a.PageID = 3` for example if i only want to get album related to page with PageID 3 – Learning Sep 10 '13 at 11:59
1

@KnowledgeSeeker . . . You would add `WHERE a.PageID = 3` after the `on` clause (remove the semicolon too). – Gordon Linoff Sep 10 '13 at 12:01
@Gordan, Thanks I missed on `;` that was generating error. Appreciate your help. – Learning Sep 10 '13 at 12:15

score 1 · Answer 2 · answered Sep 10 '13 at 11:42

1

I assume your Album table is called Album not Album_Name

SELECT .Album_Name AS Album_Name
, a.Album_Date AS Album_Date
, a.Page_ID AS PageID
, p.Image_ID AS Image_ID
, p.Image_Small AS Image_Small 
FROM Album a left outer JOIN Album_Photos p 
ON a.Album_ID = p.Album_ID
WHERE p.Image_ID = (
    SELECT MIN(Image_ID)
    FROM Album_Photos
    WHERE Album_Photos.Album_ID = Album.Album_ID
)

answered Sep 10 '13 at 11:42

CompanyDroneFromSector7G

4,291
13
54
97

Error `The multi-part identifier "Album_Name.AlbumID" could not be bound.` – Learning Sep 10 '13 at 11:53
My query doesnt have a table called Album_Name (see my comment above the code) or even a column called AlbumID, so it must be your error. However I see there is a . before the first column name which you should remove. – CompanyDroneFromSector7G Sep 10 '13 at 11:59

score 1 · Answer 3 · answered Sep 10 '13 at 12:38

1

This query would serve your purpose more closely without much complexity.

SELECT Album_Name AS Album_Name, a.Album_Date AS Album_Date, 
       p.Image_ID AS Image_ID, p.Image_Small AS Image_Small 
FROM Album_Name a left outer JOIN
Album_Photos p
    ON a.Album_ID = p.Album_ID group by p.Album_ID

This is the best query I could think off.

Reasons: * This doesn't use any subqueries, Its better to avoid subqueries, if you could do it simply * This doesn't use any row_number * looks less complex * This I have personally tested and found it working.

Explanation: Group by returns the distinct rows. Note: Your table contains column name as imageSmall so change accordingly if u want it to be image_small.

Please feel free for any clarification.

Cheers!!!

answered Sep 10 '13 at 12:38

Amit

2,495
14
22

This will return multiple rows if there is more than one `Album_photos` row. – CompanyDroneFromSector7G Sep 10 '13 at 13:04
No It won't return multiple rows, I have tested this by making the tables, worked perfectly fine. Explanation: group by p.Album_ID ensures only one row to be returned. Please do try this. – Amit Sep 11 '13 at 10:50
Actually you are right, there won't be multiple rows because it won't work at all! You can't group on a column if you don't use aggregate clauses for the other columns. You will get an error something like "Column 'whatever' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." – CompanyDroneFromSector7G Sep 11 '13 at 13:19
So did you personally test it or was that just something you put on your most to make it sound more convincing – Paul McCarthy Aug 18 '16 at 09:34

Get Distinct rows from a result of JOIN in SQL Server

3 Answers3

Linked