Combining 2 tables... 1

[manderson2006]

Here's my scenario:

I have two tables: PhotoAlbums (Album_ID, Album_Name) and Photos (Photo_ID, Photo_Name, Album_ID).

What I want to do is to display all of the Photo Albums with one randomly chosen photo from each Photo Album.

What SQL should I use to do this? Here's what I have right now:

SELECT A.Album_Name, P.Photo_Name FROM PhotoAlbums A, Photos P WHERE A.Album_ID = P.Album_ID

Obviously this doesn't work because every photo for every album will be displayed, instead of just one photo for each album.

Can someone help me here?

Thanks!

 

[niall29]

Try this

Select
a.Album_Name, p.Photo_Name
from PhotoAlbums A join Photos P on a.Album_ID = p.Album_ID

Where P.Album_ID = <Enter your Album_ID>

Hope it helps

 

[unclejimbob]

How about this quick and dirty solution ? Its not random but providing your photo name is unique withn each album it should work

SELECT A.Album_Name, max(P.Photo_Name) FROM PhotoAlbums A,
Photos P WHERE A.Album_ID = P.Album_ID group by A.Album_Name