Nested Query Problem? :(

[PotatoFro]

basic question: i'm trying to grab info from one table and sort by a value in the nested query -- below is the background and code:

i'm developing a photo upload site. I give the user the ability to upload a bunch of photos and post upload decide which albums each photo is connected to (ie. an individual photo may be under the album/label "camping trip" and "summer shots"). To accomodate i've created one table that holds all photo info (title,description, tags,id,filepath etc etc) that may change and then a separate table that links each photo to an album/label (images id, albums id).

i also allow the user to sort their images in a user defined order. that information is saved in the later table as it will be different for each album/label that the image is in.

here is the query i'm using to access all the images in the album with an id of 1:

$sql="SELECT SQL_CALC_FOUND_ROWS photos.title,photos.filename,photos.image_id,albums.order FROM photos,albums WHERE photos.image_id IN(SELECT albums.image_id FROM albums WHERE albums.album_id=1) ORDER BY albums.order LIMIT 0,20"; 

i was hoping to get a result row with the photo information (first table) as well as the order field (second table), sorted by the order field. instead (even though there are only 9 images) i get the full 20 of my limit (half of them duplicated) and i get an inacurate order result.

Am i doing this nested query incorrectly? am i even able to get the result i want?

[r937]

select sql_calc_found_rows
       photos.title
     , photos.filename
     , photos.image_id
     , albums.order
  from albums
inner
  join photos
    on photos.image_id = albums.image_id
 where albums.album_id = 1
order
    by albums.order limit 0,20