Inner join with distinct problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inner join with distinct problem

Author

Topic

almisa
Starting Member

2 Posts

Posted - 2013-08-07 : 14:22:42

table Albums:


AlbumID   Name       Date
-------------------------------
1         Acapulco   4.8.2013.
2         Germany    6.8.2013.
3         USA        7.8.2013.

tabele Pictures:


PictureID   AlbumID    Picture   PicName
----------------------------------------
1           1          a1.jpg
2           1          a2.jpg
3           1          a3.jpg
4           2          g1.jpg
5           2          g2.jpg

This is the Result I want


AlbumID   Name        Date        Picture
------------------------------------------
1         Acapulco    4.8.2013.   a3.jpg
2         Germany     6.8.2013.   g2.jpg

I tried this:


Select distinct(Albums.AlbumID), Pictures.Picture from Albums
Inner join Pictures
on Albums.AlbumID = Pictures.AlbumID
Order by Pictures.PictureID desc

but don't works.

Help Me Please.

www.jpstandard.rs

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-07 : 14:40:46

You need to add a GROUP BY and use an aggregate function instead of DISTINCT. Do you want MAX(Picture) for each AlbumID?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-07 : 14:46:46

[CODE]

DECLARE @Albums TABLE(AlbumID INT, Name VARCHAR(20), [Date] DATE);
INSERT INTO @Albums VALUES
(1, 'Acapulco', '2013-08-04'),
(2, 'Germany', '2013-08-06'),
(3, 'USA', '2013-08-07');

DECLARE @Pictures TABLE(PictureID INT, AlbumID INT, PicName VARCHAR(20));
INSERT INTO @Pictures VALUES
(1, 1, 'a1.jpg'),
(2, 1, 'a2.jpg'),
(3, 1, 'a3.jpg'),
(4, 2, 'g1.jpg'),
(5, 2, 'g2.jpg');

Select A.AlbumID, [Date], Name, PicName from @Albums A INNER JOIN
(SELECT AlbumID, MAX(PictureID) as PictureID, MAX(PicName) as PicName FROM @Pictures Group by AlbumID) P ON
A.AlbumId = P.AlbumID;

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-08 : 01:15:48

quote:
Originally posted by MuMu88

[CODE]

DECLARE @Albums TABLE(AlbumID INT, Name VARCHAR(20), [Date] DATE);
INSERT INTO @Albums VALUES
(1, 'Acapulco', '2013-08-04'),
(2, 'Germany', '2013-08-06'),
(3, 'USA', '2013-08-07');

DECLARE @Pictures TABLE(PictureID INT, AlbumID INT, PicName VARCHAR(20));
INSERT INTO @Pictures VALUES
(1, 1, 'a1.jpg'),
(2, 1, 'a2.jpg'),
(3, 1, 'a3.jpg'),
(4, 2, 'g1.jpg'),
(5, 2, 'g2.jpg');

Select A.AlbumID, [Date], Name, PicName from @Albums A INNER JOIN
(SELECT AlbumID, MAX(PictureID) as PictureID, MAX(PicName) as PicName FROM @Pictures Group by AlbumID) P ON
A.AlbumId = P.AlbumID;

[/CODE]

This will work only so long as the image names are generated sequentially. Otherwise it may choose PictureID and name from different records in subquery

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

almisa
Starting Member

2 Posts

Posted - 2013-08-08 : 03:22:34

Works great.
Thank you very much.

www.jpstandard.rs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-08 : 06:13:33

quote:
Originally posted by almisa

Works great.
Thank you very much.

www.jpstandard.rs

Hope your image names always get generated in a sequential manner.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources