Forum OpenACS Q&A: Response to photodb for openACS
The main issue is that since the states table doesn't have a 'null' or 'empty' entry, all joins will the states table will fail if photodb.state is null or empty. This could be fixed with an outer join, once those are available in pg7.1.
The workaround that I used is to query for all of the data about the photo without doing a join with the states table. Then later in the page, when we're about to show the location info, then we'll do a query on the states table - only if ph_photos.state is not null.
So this version should capture and preserve city, state, zip & country and also do the right thing if those are not present.