I'm having trouble constructing a MySQL statement. Basically I need to select one row per member (from the members table) which includes their lowest numbered photo (from the photos table) where they use the same ID number.
My statement as it is right now is basically:
SELECT members., photos. FROM members, photos WHERE members.ID = photos.ID AND photos.order = 1 AND members.expiration >= '2002-04-09' GROUP BY members.ID ORDER BY members.lastname ASC, members.firstname ASC, members.middleinitial ASC
There's a lot more to the WHERE clause, but that's the gist of it. DISTINCT won't work as far as I know because of the multiple fields in the photos table with different entries. I need to make it so if the user doesn't have any photo.order entries set to 1 that it will return the next lowest number for that field.
Is that possible?
Thanks!
