SQL/ classic asp issue

SQL, joins, asp, photos

[KRMwebdesign]

Hi All,
I am trying to put together a number of photo albums. The photo albums show up as images on a page using a repeat region. When you click on the photo album image that will show the photos in the album.

I have set up 2 tables Photos and PhotoAlbums. Each record in the Photos table has an PHotoAlbumID to link it to the photo album.

What I want is to show the first image in the album for each photo album cover. So if the first imageID is 6 then image 6 should show up on the photo albums page as the Album cover if you understand what I mean?

This is the SQL statement I have at the moment but I know it won't do what I want:
"Select * FROM PhotoAlbums left join Photos on PhotoAlbums.AlbumID = Photos.AlbumID ORDER BY Photos.AlbumID asc"

I wonder can anyone enlighten me on what statement I could be putting here?

[kb5nju]

Are you trying to grab just the first photo assigned to each album? If that's the case, you may have to loop through the AlbumID numbers in that table and pull each one separately.

Something like this might work in that case:

SELECT TOP 1 *
FROM Photos, PhotoAlbums
WHERE Photos.AlbumID = PhotoAlbums.AlbumID AND PhotoAlbums = 1

[KRMwebdesign]

Thanks for the reply kb5nju.
I will try this and see what happens. Thanks again.

[KRMwebdesign]

I keep getting this error:

Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters

[kb5nju]

Yup - I forgot a part. Fix the WHERE clause to end like this:

AND PhotoAlbums.AlbumID = 1

[KRMwebdesign]

I should have seen that myself tbh :). Thanks for the help. I'll try it again.

Many thanks

[KRMwebdesign]

Hi again,
I have tested this select statement on my page "select * from PhotoAlbums left join Photos on PhotoAlbums.AlbumID = Photos.AlbumID ORDER BY Photos.picID desc" and it is working okay. There is only 1 problem with it. It is taking every picture in the "Photos" table and showing them as an Album cover but I am just looking for 1 image out of each album to be the album cover. Does anyone know how I can achieve this?

I didn't get any results for the above using this statement "SELECT TOP 1 *
FROM Photos, PhotoAlbums
WHERE Photos.AlbumID = PhotoAlbums.AlbumID AND PhotoAlbums = 1 " I'm afraid.

[Demaestro]

you can simply add a limit clause to the end of the query

select * from PhotoAlbums left join Photos on PhotoAlbums.AlbumID = Photos.AlbumID ORDER BY Photos.picID desc limit 1

[kb5nju]

"limit 1" - that's a new one on me. Thanks.

[KRMwebdesign]

I am using MsAccess database at the moment and "Limit 1" isn't working. I plan to migrate to MySQL but not until I develop the software properly first. Any other suggestions?

[KRMwebdesign]

Okay, I have this statement working "Select Top 1 * from PhotoAlbums left join Photos on PhotoAlbums.AlbumID = Photos.AlbumID ORDER BY Photos.picID desc" but it is only returning the first photo album and not the rest. Is there anything I can do to fix this?

[Demaestro]

Try removing the TOP 1... that is telling it to only return 1

[KRMwebdesign]

Okay but if I do that it returns every photo as a photo album but not every album with an image from the photos table :s

[kb5nju]

KRM - you will need to write a query in to pull out all the AlbumID records from that table, then loop through that list and pull the first picture out of each Album running the query you just got working for each number in the list.

[Demaestro]

kb5 is right. You need 2 queries and a loop

pseudo code looks like this:

sql = "SELECT * FROM Photos"

photo_data = sql_execute(sql)

for (photo in photo_data) {
cover_sql = "Select Top 1 * from PhotoAlbums where PhotoAlbums.AlbumID = " photo['AlbumID']

cover_data = sql_execute(cover_sql)

print cover_data[0][0]
}

[mrmobility]

Could you do something like this?

SELECT * FROM
PhotoAlbums
INNER JOIN
(SELECT MIN(ImageId) AS ImageId,AlbumID
FROM Photos
GROUP BY AlbumId) FirstImages
ON
(FirstImages.AlbumId = PhotoAlbum.AlbumId)
INNER JOIN
Photos
ON
(FirstImages.ImageId = Photos.ImageId)