I have a database with a set of images in it. Each image has an ID number as well as a category and sub_category field. When a user clicks "next image" I want the db to select the next image in that category and sub_category. The code I have works until i am at the first of last image in the category or sub_category. then it returns null for both of the id's I return. here is my query:

SELECT min( p1.id ) , min( p2.id )
FROM pictures AS p1, pictures AS p2
WHERE p1.id > 46 AND p1.sub_cat = '1' AND p2.sub_cat = '1';


after this, I determine if min(p1.id) is null, then use min(p2.id) as the id. So it takes the smallest id after 46, or the smallest id in the column (with the appropriate cat & sub_cat values). This, however, returns 2 nulls. when i remove the cat & sub_cat limitations, it works fine. any ideas?

Hi rico, welcome to GIDForums

Sorry no-one has got to your question yet, we'll see if someone can figure out your problem.

Rob

Hey, it's no problem -- I work today and tonight and can't get to it until much later. Maybe if I clarify what I'm looking for, it'll be easier:

I want to return to picture Id numbers
(1) the smallest id, greater than the current one that has the same cat and sub_cat
(2) the smallest id overall that has the same cat and sub_cat.


i do this so that when i reach the last pic in that category, it wraps to the first.


thanks.