sql querry help PLEASE!!!

[xx123xxx]

hi all,

got an easy oen here...

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 records 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". if there are multiple records for a gallery then the most curent is the one i need to check for if it is trashed. there is a date field for this.

the tables are linked by galelry_id

thank you all.

 

[rtmpxr]

I'm not sure what "gallery_type" is doing here, but this is an example:

SELECT DISTINCTROW gallery_name FROM galleries LEFT OUTER JOIN projections on galleries.gallery_id=projections.gallery_id WHERE (projections.gallery_id IS NULL) OR ("trashed" in (SELECT TOP 1 gallery_type FROM Projections where gallery_id=galleries.gallery_id ORDER BY gallery_date DESC)) LIKE "*gomez*" ORDER BY gallery_name

Now, what they means:
-projections.gallery_id IS NULL- gives you all galleries which do not exist in projections table
-SELECT TOP 1 gallery_type FROM Projections where gallery_id=galleries.gallery_id ORDER BY gallery_date DESC- gives the FIRST record from the set of galleries, MOST RECENT since all records are ordered descended by date. So I select the gallery_type from the most recent record of galleries and compared with the value "trashed".

I don't believe this is the exact solution, but you can figure out what you must to do.