www.webdeveloper.com
Results 1 to 6 of 6

Thread: Help w/ JOIN - Require 1 row from ony-to-many for each unique id

  1. #1
    Join Date
    Jun 2007
    Location
    Sydney, Australia
    Posts
    7

    Question Help w/ JOIN - Require 1 row from ony-to-many for each unique id

    MYSQL 5.0

    I have 2 tables.
    One full of stuff related to apple trees
    The other keeps a log of what happens to the apple trees.
    So when I change the status of an apple try i make a row in the log saying the status changed for this apple tree from to and when and by who.

    SO what I want to do is create a VIEW of the most recent log entry for each apple tree thus creating a LastModified table. I know I could add a field "ModifiedWhen" & "ModifiedBy" but since this is a made up scenario that best describes my real much more complex scenario. I'm interested in way I've described more then how to best do this simplistic example.

    Ok now to put all this visually....

    Code:
    ----------------------------
               Apples
    ----------------------------
     ID | Status       | Color
    ----------------------------
      1 | Fully Grown  |  Red
      2 | Just Planted |  Green
      3 | Seedling     |  Green
      4 | Seedling     |  Red 
    
    -----------------------------------------------------------------------------------
                                        ApplesLoG
    -----------------------------------------------------------------------------------
     ID | R_A_ID | Who   | When       | Notes
    -----------------------------------------------------------------------------------
      1 |   1    |  John | 2007-05-06 | Planted
      2 |   1    |  Tim  | 2007-05-12 | Seedling Now
      3 |   2    |  John | 2007-05-13 | Just Planted
      4 |   1    |  Tim  | 2007-05-14 | Got some leaves
      5 |   3    |  Tim  | 2007-05-15 | Planted
      6 |   4    |  John | 2007-05-16 | Planted
      7 |   1    |  Tim  | 2007-05-16 | eh, its a Red one now, thought it was green...
      8 |   1    |  Tim  | 2007-05-17 | Fully Grown apple try now - yum!
      9 |   3    |  Tim  | 2007-05-19 | Seedling Now
      10|   4    |  John | 2007-05-20 | Seedling Now
    
    -----------------------------
         ApplesModified (View)
    -----------------------------
    ApplesID | Who  | When
    -----------------------------
        1    | Tim  | 2007-05-17
        2    | John | 2007-05-13
        3    | Tim  | 2007-05-19
        4    | John | 2007-05-20
    There that sums up pretty good what I'm trying to do. Just select the most recent entry from the Log table as it relates to the Apples.
    My query building so far has got me all the log results as a outer join, then I try limiting it and I end up with 1 result and thats it....

    Please help :D

  2. #2
    Join Date
    Jul 2005
    Location
    South Carolina
    Posts
    395
    What does your query look like so far?
    I can solve differential equations and build huge databases, but I have no idea how to change my oil.

  3. #3
    Join Date
    Jul 2005
    Location
    South Carolina
    Posts
    395
    This may do what you're looking for:
    Code:
    select ta.max_appid,  Who, ta.max_date
    from ApplesLoG
    join
    (select R_A_ID as max_appid, max(When) as max_date
    from ApplesLoG
    group by R_A_ID) ta
    on ta.max_appid = ApplesLoG.R_A_ID
    and ta.max_date = ApplesLoG.When
    Note that if there are multiple entries for the same ID on the same date, that the query should return all of them.
    I can solve differential equations and build huge databases, but I have no idea how to change my oil.

  4. #4
    Join Date
    Jun 2007
    Location
    Sydney, Australia
    Posts
    7
    Quote Originally Posted by bubbisthedog
    Note that if there are multiple entries for the same ID on the same date, that the query should return all of them.
    Thanks, Will give that a go - yeah the date is actually a TIMESTAMP field, I just made it date so as to save me typing more in the example :)

    I knew would have to involve sub queries, just my sub query skillz are lacking...

    I could do this problem in a for loop in the program of course but the whole point of having a proper dbms is kinda to off load some of this to it ;)

  5. #5
    Join Date
    Jul 2004
    Posts
    300
    nm queston answered.

  6. #6
    Join Date
    Jun 2007
    Location
    Sydney, Australia
    Posts
    7
    MYSQL 5.0 as mentioned at the very top - i read the sticky - it said mention this

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles