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. http://www.aspfaq.com/5009

    Ray at home

    "the other john" <> wrote in message
    news:...
    > 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!!!
    >
     
    Ray Costanzo [MVP], Jul 18, 2005
    #2
    1. Advertising

  3. Oops - I assumed he was using Access and provided a solution for Access.

    To "the other john":
    if you are not using Access, the "Last" function may not exist in the brand
    of sql you are using. The second example is the one that will work in most
    databases.

    Bob Barrows
    Ray Costanzo [MVP] wrote:
    > http://www.aspfaq.com/5009
    >
    > Ray at home
    >
    > "the other john" <> wrote in message
    > news:...
    >> 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!!!


    --
    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
    #3
  4. You assumed correctly on the Access. I'll try this out and get back
    today.
     
    the other john, Jul 18, 2005
    #4
  5. ooooh boy, my head is spinning on the aliasing.....wow. I start losing
    it when you start useing "q". p for the pictures table and g for the
    galleries table....what is the q?

    Thanks!
     
    the other john, Jul 19, 2005
    #5
  6. ok, I'm getting this error.....

    Microsoft JET Database Engine error '80004005'

    The specified field 'p.PK_Picture_Gallery_ID' could refer to more than
    one table listed in the FROM clause of your SQL statement.


    this is the statement I wrote based on what you suggested. What did I
    get wrong?

    gallerySQL = "SELECT g.fld_Picture_Gallery_fileName,
    p.PK_Picture_Gallery_ID FROM (tbl_pictures_galleries AS g " & _
    "INNER JOIN tbl_Pictures AS p ON g.PK_Picture_Gallery_ID =
    p.FK_Picture_galleryID) " & _
    "INNER JOIN (SELECT p.FK_Picture_galleryID,
    Max(p.fld_picture_DateTime) AS LastDate FROM tbl_Pictures AS p GROUP BY
    p.fld_picture_galleryID) " & _
    "AS q ON p.fld_picture_galleryID = q.GalleryID and
    p.fld_picture_DateTime=q.LastDate;"

    Thanks!
     
    the other john, Jul 19, 2005
    #6
  7. the other john wrote:
    > ooooh boy, my head is spinning on the aliasing.....wow. I start
    > losing it when you start useing "q". p for the pictures table and g
    > for the galleries table....what is the q?
    >

    To me, the aliases make it much more readable. The q is the alias for the
    subquery (the select statement enclosed in parenthes). With a subquery, aka
    derived or virtual table, there is no physical table so you HAVE to use an
    alias.

    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 19, 2005
    #7
  8. the other john wrote:
    > ok, I'm getting this error.....
    >
    > Microsoft JET Database Engine error '80004005'
    >
    > The specified field 'p.PK_Picture_Gallery_ID' could refer to more than
    > one table listed in the FROM clause of your SQL statement.
    >
    >
    > this is the statement I wrote based on what you suggested. What did I
    > get wrong?
    >
    > gallerySQL = "SELECT g.fld_Picture_Gallery_fileName,
    > p.PK_Picture_Gallery_ID FROM (tbl_pictures_galleries AS g " & _
    > "INNER JOIN tbl_Pictures AS p ON g.PK_Picture_Gallery_ID =
    > p.FK_Picture_galleryID) " & _
    > "INNER JOIN (SELECT p.FK_Picture_galleryID,
    > Max(p.fld_picture_DateTime) AS LastDate FROM tbl_Pictures AS p GROUP
    > BY p.fld_picture_galleryID) " & _
    > "AS q ON p.fld_picture_galleryID = q.GalleryID and
    > p.fld_picture_DateTime=q.LastDate;"
    >


    Why aren't you testing this in Access using the Query Builder? Always get
    your queries running in Access before attempting to make them run from ASP.
    That way when you run into problems, you know where start looking for the
    problem.

    I'm not sure where the problem is. The example I posted worked fine when I
    tried it in Access. I suggest modifying the subquery - since the subquery
    only has a single table, it does not need an alias: the field names do not
    need to be qualified.

    "INNER JOIN (SELECT FK_Picture_galleryID,
    Max(fld_picture_DateTime) AS LastDate FROM tbl_Pictures GROUP BY
    fld_picture_galleryID) " & _
    "AS q

    If you feel you absolutely MUST qualify all your field names, then use a
    different alias than p in the subquery


    I would not have expected there to be any confusion between the table
    aliases given that one alias was inside the subquery, however, this would
    not be the first time I've been surprised by Jet.

    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 19, 2005
    #8
  9. I'm still kind of a newbie. Not quite but not far removed. I'm workin
    at it and I'm sure these will get easier. Just when I think "oh, ok,
    I'm getting kind of good at this" something comes up and I go "OOooooh,
    well, not that good yet" ;-P Hope I didn't imply that what you're
    telling me isn't good, on the contrary....hoping to get there myself is
    all ;-)
     
    the other john, Jul 19, 2005
    #9
  10. Oh, I should have added that I don't really know how to use the Query
    build in Access that well. I've played around with it but not knowing
    how to make it flexible enough to just "do" something I want it to I
    end up just writing the statements from stratch and testing until I
    either tear out my hair or get it right. I used Views with sql server
    via access and that seems more logical or at least easier to use than
    Access. I've still got a lot to learn...just need to find places to do
    that.
     
    the other john, Jul 19, 2005
    #10
  11. the other john wrote:
    > Oh, I should have added that I don't really know how to use the Query
    > build in Access that well.


    Don't let that stop you. Open the Query Builder and switch to SQL View - no
    need to use the GUI if you don't want to. The iea is to create and test your
    queries in the environment where they will actually run, using the tools in
    access to help you optimize their performance (the Analyzer is a very good
    tool to use. depending on the version of access you are using, it can be
    found in Tools/Analyze). The problem with doing everything from asp is that
    you never have a chance to perform optimizations. Also, if you get into the
    habit of saving and parameterizing your queries, you will find them a lot
    simpler (and more secure) to execute from asp as opposed to the klunky and
    insecure dynamic sql you are creating. See:

    http://mvp.unixwiz.net/techtips/sql-injection.html
    http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

    http://groups.google.com/groups?hl=...=1&selm=
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd


    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 19, 2005
    #11
  12. This is a query I ran in query builder. It doesn't do everything I want
    it to do yet but I have some questions....
    SELECT tbl_Pictures.FK_picture_galleryID,
    tbl_Pictures_Galleries.PK_Picture_Gallery_ID,
    tbl_Pictures.fld_picture_DateTime,
    tbl_Pictures_Galleries.fld_Picture_Gallery_Name
    FROM tbl_Pictures_Galleries INNER JOIN tbl_Pictures ON
    tbl_Pictures_Galleries.PK_Picture_Gallery_ID =
    tbl_Pictures.FK_picture_galleryID
    WHERE (((tbl_Pictures_Galleries.PK_Picture_Gallery_ID)=5))
    ORDER BY tbl_Pictures.fld_picture_DateTime DESC;

    what's with all the parentheses??

    Also, I tried to filter by using MAX() on the DateTime field but got an
    error that said...
    "You tried to execute a query that does not include the specified
    expression'FK_picture_galleryID' as part of an aggregate function". I
    don't know what this means. I know that aggregate means to summarize
    but I don't know why this won't work.

    Also, I'll have to dynamically loop through the gallery ID's in the
    WHERE clause. Do I just open a seperate recordset for these and embed
    the statement I've been working on above within it?

    Thanks again! My deadline is getting close and I'm starting to sweat
    now.
     
    the other john, Jul 19, 2005
    #12
  13. the other john wrote:
    > This is a query I ran in query builder. It doesn't do everything I
    > want it to do yet but I have some questions....
    > SELECT tbl_Pictures.FK_picture_galleryID,
    > tbl_Pictures_Galleries.PK_Picture_Gallery_ID,
    > tbl_Pictures.fld_picture_DateTime,
    > tbl_Pictures_Galleries.fld_Picture_Gallery_Name
    > FROM tbl_Pictures_Galleries INNER JOIN tbl_Pictures ON
    > tbl_Pictures_Galleries.PK_Picture_Gallery_ID =
    > tbl_Pictures.FK_picture_galleryID
    > WHERE (((tbl_Pictures_Galleries.PK_Picture_Gallery_ID)=5))
    > ORDER BY tbl_Pictures.fld_picture_DateTime DESC;
    >
    > what's with all the parentheses??


    Yeah, I know. The Access Builder takes no chances. Everyting it considers to
    be an expression is surrounded by parentheses to prevent the query engine
    from misinterpreting anything. The Query Builder does not always know
    exactly what you mean so it takes no chances. The first thing I used to do
    when using the Query Builder after switching to SQL View was: remove all the
    damn parentheses ... except for the ones surrounding the joins in the FROM
    clause - those seem to be required by Jet. Anyways, the above generated SQL
    would be changed by me to (you don't seem to like using the shorter aliases,
    so I'll leave the generated alieases used in the field qualifications
    alone):

    SELECT tbl_Pictures.FK_picture_galleryID,
    tbl_Pictures_Galleries.PK_Picture_Gallery_ID,
    tbl_Pictures.fld_picture_DateTime,
    tbl_Pictures_Galleries.fld_Picture_Gallery_Name
    FROM tbl_Pictures_Galleries INNER JOIN tbl_Pictures ON
    tbl_Pictures_Galleries.PK_Picture_Gallery_ID =
    tbl_Pictures.FK_picture_galleryID
    WHERE tbl_Pictures_Galleries.PK_Picture_Gallery_ID=5
    ORDER BY tbl_Pictures.fld_picture_DateTime DESC;


    >
    > Also, I tried to filter by using MAX() on the DateTime field but got
    > an error that said...
    > "You tried to execute a query that does not include the specified
    > expression'FK_picture_galleryID' as part of an aggregate function". I
    > don't know what this means. I know that aggregate means to summarize
    > but I don't know why this won't work.

    Aggregate = result of summary (aggregate) function such as SUM,
    MAX,MIN,FIRST, etc.

    You cannot aggregate without using GROUP BY, unless all the fields in the
    SELECT clause are aggregates. This also applies to the WHERE clause. In
    fact, an aggregation cannot be used in the WHERE clause. If you need to
    filter on an aggregate, it needs to be done in the HAVING clause. See the
    link I cite below.
    And if you do use GROUP BY, the only non-aggregates allowed in the select
    are the columns listed in the GROUP BY clause.

    Here's an old post I made that hopefully explains this:
    *****************************************
    Say you have a table with two columns containing the following 4 rows of
    data:

    Col1 Col2
    1 28
    1 33
    2 5
    2 8

    Now you decide to create a grouping query:
    select Col1 From table Group By Col1
    You would get these results:
    1
    2

    Now you decide to add Col2 to the select list:
    select Col1,Col2 From table Group By Col1
    Here are the results:
    1 ?
    2 ?

    What do you replace the ?'s with? Do you see the problem?

    Bottom line: You have to tell the query engine how to aggregate Col2 (min,
    max,sum, avg,count, etc.) so that you wind up with a single row for each
    value in Col1.

    Even if Col2 contains the value 5 in every row, you still have to tell the
    engine how to aggregate it. The query parser does not know what's in your
    table: all it can see is that there is a column in the select list that
    needs aggregation.
    ***************************************************
    You should also read this:
    http://groups-beta.google.com/group/comp.databases.ms-sqlserver/msg/ec194ab2109662c8
    >
    > Also, I'll have to dynamically loop through the gallery ID's in the
    > WHERE clause.


    I don't understand.

    > Do I just open a seperate recordset for these and embed
    > the statement I've been working on above within it?


    I don't know. I don't understand what you're trying to do.
    >
    > Thanks again! My deadline is getting close and I'm starting to sweat
    > now.

    Sorry, but if you're looking for me to write it for you, I've got my own job
    to do ... ;-)

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Jul 19, 2005
    #13
  14. Thanks Bob, this is helping a lot. I'm just freakin' because I only
    have a few more days to figure this out 8-o

    Basically all I'm trying to do is select and display the latest picture
    from each gallery. What is throwing me is that all the pictures are in
    one table and only linked to a specific gallery via a FK in a gallery
    table. So if there were 6 galleries I would need to display the latest
    picture from each gallery in a row or something. What I was saying
    earlier is all I can think of doing at my level is....

    Open a recordset of gallery names and ID's
    .......
    Setup a For Each Loop
    .......
    Open another recordset that selects the latest picture from one gallery
    .......
    Display that picture and it's gallery name
    .......
    Go to the next gallery with it's latest picture
    .......
    Loop until all galleries have been displayed with their latest picture
    .......
    Close the Second recordset
    .......
    Close the First recordset

    All I'm asking at this point is this an alright way to do it or will it
    not work? I'm trying to avoid blind alleys at this point.

    Thanks again!!
     
    the other john, Jul 19, 2005
    #14
    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:
    792
    =?Utf-8?B?dHJlbGxvdzQyMg==?=
    Feb 20, 2004
  2. Harvey
    Replies:
    0
    Views:
    750
    Harvey
    Jul 16, 2004
  3. Harvey
    Replies:
    1
    Views:
    881
    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:
    703
    Eliyahu Goldin
    Mar 9, 2006
  5. Buster

    Help, Help, Help

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

Share This Page