SQL Query Help - inner joins and distinct results

Question

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 Answer

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.