HELP, How do I do this??

Discussion in 'ASP General' started by the other john, Jul 17, 2005.

  1. This should be fairly basic but I can't think of how to do this and I'm
    running out of time!

    I am developing a picture gallery and I can't figure out how to select
    "one" picture from each gallery. My DB is configured like this...

    tbl_pictures
    this contains all pictures in the picture gallery.

    tbl_pictures_galleries
    this contains all the picture gallery names that can be created by the
    client. It's primary key acts as a foreign key in the tbl_pictures
    table.

    My problem: I don't know how to select the latest picture from each
    gallery (identified by FK) via a loop. This should be so simple but
    I'm just not seeing it right now.

    Help greatly appreciated!!

    Thanks!!!
    the other john, Jul 17, 2005
    #1
    1. Advertising

  2. the other john wrote:
    > This should be fairly basic but I can't think of how to do this and
    > I'm running out of time!
    >
    > I am developing a picture gallery and I can't figure out how to select
    > "one" picture from each gallery. My DB is configured like this...
    >
    > tbl_pictures
    > this contains all pictures in the picture gallery.
    >
    > tbl_pictures_galleries
    > this contains all the picture gallery names that can be created by the
    > client. It's primary key acts as a foreign key in the tbl_pictures
    > table.
    >
    > My problem: I don't know how to select the latest picture from each
    > gallery (identified by FK) via a loop. This should be so simple but
    > I'm just not seeing it right now.
    >


    In a truly relational database, you would need to have a field in the
    tbl_pictures table to store when the picture was stored so you could use it
    to identify the "last" picture for each gallery. However, this is one of the
    cases where Access is not truly relational. JetSQL has an aggregate function
    called "LAST". Here is what the query would look like, using my guesses as
    to your field names (it would have helped to show us your field names as
    well as their datatypes):

    SELECT g.GalleryName, Last(p.PictureID) AS LastOfPictureID
    FROM tbl_pictures_galleries AS g INNER JOIN
    tbl_pictures AS p ON g.GalleryID = p.GalleryID
    GROUP BY g.GalleryName

    If you do have a field (perhaps named DateAdded) to truly identify the last
    record for each gallery, you will get more predictable results using this
    query:

    SELECT g.GalleryName, p.PictureID
    FROM (tbl_pictures_galleries AS g INNER JOIN
    tbl_pictures AS p ON g.GalleryID = p.GalleryID) INNER JOIN
    (SELECT p.GalleryID, Max(p.DateAdded) AS LastDate
    FROM tbl_pictures AS p
    GROUP BY p.GalleryID) as q
    ON p.GalleryID = q.GalleryID and p.DateAdded=q.LastDate

    HTH,
    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Jul 18, 2005
    #2
    1. Advertising

  3. the other john

    Bullschmidt Guest

    Well if each "gallery" is a separate table (instead of just different
    data for one field in the main table) then you may need to use UNION
    somehow in your SQL statement.

    Best regards,
    J. Paul Schmidt, Freelance ASP Web Developer
    http://www.Bullschmidt.com
    ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...

    <<
    This should be fairly basic but I can't think of how to do this and I'm
    running out of time!

    I am developing a picture gallery and I can't figure out how to select
    "one" picture from each gallery. My DB is configured like this...

    tbl_pictures
    this contains all pictures in the picture gallery.

    tbl_pictures_galleries
    this contains all the picture gallery names that can be created by the
    client. It's primary key acts as a foreign key in the tbl_pictures
    table.

    My problem: I don't know how to select the latest picture from each
    gallery (identified by FK) via a loop. This should be so simple but
    I'm just not seeing it right now.

    Help greatly appreciated!!

    Thanks!!!
    >>



    *** Sent via Developersdex http://www.developersdex.com ***
    Bullschmidt, Jul 21, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?dHJlbGxvdzQyMg==?=

    HELP! HELP! HELP! Opening Web Application Project Error

    =?Utf-8?B?dHJlbGxvdzQyMg==?=, Feb 20, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    764
    =?Utf-8?B?dHJlbGxvdzQyMg==?=
    Feb 20, 2004
  2. Harvey
    Replies:
    0
    Views:
    693
    Harvey
    Jul 16, 2004
  3. Harvey
    Replies:
    1
    Views:
    837
    Daniel
    Jul 16, 2004
  4. =?Utf-8?B?S2ltb24gSWZhbnRpZGlz?=

    HELP - HELP - HELP

    =?Utf-8?B?S2ltb24gSWZhbnRpZGlz?=, Mar 9, 2006, in forum: ASP .Net
    Replies:
    4
    Views:
    679
    Eliyahu Goldin
    Mar 9, 2006
  5. Buster

    Help, Help, Help

    Buster, Oct 4, 2003, in forum: Java
    Replies:
    3
    Views:
    470
    Saager
    Oct 30, 2003
Loading...

Share This Page