×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

sql querry help PLEASE!!!

sql querry help PLEASE!!!

sql querry help PLEASE!!!

(OP)
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.

RE: sql querry help PLEASE!!!

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources