SQL Query Help - inner joins and distinct results

[tedstar]

Hi,

ASP VB, SQL Server

I have a database structure with 3 tables - users, albums and photos. each user has a unique id, each album record has a unique albumid and also contains a column with userid. each photo record has a unique id as well as storing the userid and the album in which the image belongs.

I'm trying to write a query that will return the list of albums for a particular user (based on a userid query string) and which will also bring back the id of the first record in the photo table for each of these albums.

the closest I am getting is to run a query to select albumid from albums where userid=varuserid with an inner join on the photos table to pull out the photo id - problem I then have is that it pulls out every photo from the photos table where userid=varuserid, so when I do a repeat region to display a list of albums for a certain user, it produces a list of all photos where userid=varuserid

I really want it to just return a list of album ids based on the userid variable, but also to return the first record from the photos table for each of these albumids

I've tried various combinations of inner joins, select distinc etc but not joy.

any suggestions would be much appreciated as am floundering here...

[Newsgroup_User · Accepted solution]

First, you have to define "first" in regards to photos. Is there a
timestamp? Are they numbered inside the album? Do you really care which is
"first", or do you just want any one photo? You also neglected to state if
empty photo albums were allowed. I've assumed that empty albums are not
allowed.

Whatever you decide, the answer is going to be similar.
SQL Server tends to perform better with joins than with subqueries. You'll
see this type of query written with subqueries most often, and there's
nothing wrong with that, but I'm going to use a join on a derived table. I
don't have any column names (hint, hint), so I made them up, but the
comments should help you out.

SELECT A.AlbumID, A.Title, P.PhotoID, P.Caption, P.ImagePath
FROM dbo.Albums A
--construct a derived table consisting of the lowest photo ID for each
album.
INNER JOIN (SELECT AlbumID, MIN(PhotoID) AS FirstPhoto FROM dbo.Photos
GROUP BY AlbumID) AS PM ON A.AlbumID=PM.AlbumID
--get the photo details for the photo indicated in the above table
INNER JOIN dbo.Photos P ON A.AlbumID=P.AlbumID AND
PM.FirstPhoto=P.PhotoID
WHERE A.UserID='some user'


"tedstar" <webforumsuser@macromedia.com> wrote in message
news:ee4pfn$de$1@forums.macromedia.com...
> I'm trying to write a query that will return the list of albums for a
> particular user (based on a userid query string) and which will also bring
> back
> the id of the first record in the photo table for each of these albums.

[tedstar]

Hi there,

Thank you VERY MUCH. I tweaked the column names and it works.

Sorry for not providing more detail in original post but was after a point in the right direction in terms of joins/sub-queries...anyway, your reply was more than helpful and I can crack on now,

Thanks again,

Ed

[Newsgroup_User]

No problem. :)

"tedstar" <webforumsuser@macromedia.com> wrote in message
news:ee6j20$74s$1@forums.macromedia.com...
> Hi there,
>
> Thank you VERY MUCH. I tweaked the column names and it works.
>
> Sorry for not providing more detail in original post but was after a point
> in
> the right direction in terms of joins/sub-queries...anyway, your reply was
> more
> than helpful and I can crack on now,
>
> Thanks again,
>
> Ed
>