i got 2 tables in sql srvr2000. one contains galleries and the other projections.
i want to return all galleries that match a nile like '%gomez%' but only if the gallery_id is not 'trashed' in the projected table.
here is the curver ball. the projected table can have no records or multiple for any given gallery. if it has no record that is fine but if it has many i need to find the most recent and make sure its gallery_type is not "trashed".
Unless you've got some kinda datestamp or sequence ID you cannot do this - db records have no inherent 'order'. I'm off home now - if you give more details (eg the schemas for the tables) I'll have a look tomorrow for ya (and that's service ;-) )
Can a gallery be 'untrashed' again, or once 'trashed' does it die forever? This is the easiest case:
SELECT <some stuff> where gallery_ID not in (Select gallery_ID from projected where gallerytype = 'trashed') and gallery nile like '%gomez%'
(ie we exclude a list of galleryID's that are 'trashed' and return the rest - date is irrelevent if the 'trashed' state is as permenant as the name suggests!)
hope this helps - otherwise I'll have another look for you mid-jan - gotta dash :)
Topic Locked
Posted - 23 December 2004 : 11:48:06
