Professional Web Applications Themes

Items with max from another table - MySQL

Hello, I have 3 tables: Actions, Advisors, Forecasts. Forecasts contains the fiels ActionID, AdvisorID and Date. I want to retrieve, for each couple (action, advisor), the max date that can be found in Forecasts, or NULL if the couple is not present in the Forecasts table. I thought I would do it that way: SELECT ActionID, AdvisorID, Max(Date) FROM Forecasts RIGHT OUTER JOIN Actions ON Actions.ID = Forecasts.ActionID RIGHT OUTER JOIN Advisors ON Advisors.ID = Forecasts.AdvisorID GROUP BY ActionID, AdvisorID But for some reason that I'm not sure to understand, this seems to return the same datas as (at least ...

  1. #1

    Default Items with max from another table

    Hello,

    I have 3 tables: Actions, Advisors, Forecasts. Forecasts contains the fiels
    ActionID, AdvisorID and Date.
    I want to retrieve, for each couple (action, advisor), the max date that can
    be found in Forecasts, or NULL if the couple is not present in the Forecasts
    table.
    I thought I would do it that way:

    SELECT ActionID, AdvisorID, Max(Date) FROM Forecasts RIGHT OUTER JOIN
    Actions ON Actions.ID = Forecasts.ActionID RIGHT OUTER JOIN Advisors ON
    Advisors.ID = Forecasts.AdvisorID GROUP BY ActionID, AdvisorID

    But for some reason that I'm not sure to understand, this seems to return
    the same datas as (at least this doesn't return what I want :))

    SELECT ActionID, AdvisorID, Max(Date) FROM Forecasts GROUP BY ActionID,
    AdvisorID

    How can this be achieve and what is my error?

    Thank you

    --
    Vincent


    Vincent Lascaux Guest

  2. #2

    Default Re: Items with max from another table

    > I have 3 tables: Actions, Advisors, Forecasts. Forecasts contains the
    > fiels ActionID, AdvisorID and Date.
    > I want to retrieve, for each couple (action, advisor), the max date that
    > can be found in Forecasts, or NULL if the couple is not present in the
    > Forecasts table.
    Hum... I'm not sure what I said is clear... so let take an example :)

    Let's say Actions contains two lines (ID 0 and ID 1), Advisors contains two
    lines (ID 0 and ID 1) and Forecasts contains three lines (ActionID 0,
    Advisor 0, date1), (ActionID 0, Advisor 0, date2), (ActionID 1, AdvisorID 0,
    date3)

    The output I want is
    ActionID 0, AdvisorID 0, max(date1, date2)
    ActionID 0, AdvisorID 1, NULL
    ActionID 1, AdvisorID 0, date3
    ActionID 1, AdvisorID 1, NULL

    (thus if there are n actions and m advisors, the output has exactly n*m
    lines)

    Thank you for your help

    --
    Vincent


    Vincent Lascaux Guest

  3. #3

    Default Re: Items with max from another table

    "Vincent Lascaux" <nospamnospam.org> wrote in message
    news:43eed7e4$0$304$626a14cenews.free.fr...
    > SELECT ActionID, AdvisorID, Max(Date) FROM Forecasts RIGHT OUTER JOIN
    > Actions ON Actions.ID = Forecasts.ActionID RIGHT OUTER JOIN Advisors ON
    > Advisors.ID = Forecasts.AdvisorID GROUP BY ActionID, AdvisorID
    ActionID and AdvisorID come from the Forecasts table, so they are NULL where
    there is no matching row in the Forecasts. Thus all such cases are folded
    into one group.

    Try using GROUP BY Actions.ID, Advisors.ID instead.

    Regards,
    Bill K.


    Bill Karwin Guest

  4. #4

    Default Re: Items with max from another table


    "Vincent Lascaux" <nospamnospam.org> wrote in message
    news:43eed7e4$0$304$626a14cenews.free.fr...
    > Hello,
    >
    > I have 3 tables: Actions, Advisors, Forecasts. Forecasts contains the
    > fiels ActionID, AdvisorID and Date.
    > I want to retrieve, for each couple (action, advisor), the max date that
    > can be found in Forecasts, or NULL if the couple is not present in the
    > Forecasts table.
    > I thought I would do it that way:
    >
    > SELECT ActionID, AdvisorID, Max(Date) FROM Forecasts RIGHT OUTER JOIN
    for one thing, Date is a reserved word (a type in fact), so don't use it for
    a table or column name.
    > Actions ON Actions.ID = Forecasts.ActionID RIGHT OUTER JOIN Advisors ON
    > Advisors.ID = Forecasts.AdvisorID GROUP BY ActionID, AdvisorID
    >
    > But for some reason that I'm not sure to understand, this seems to return
    > the same datas as (at least this doesn't return what I want :))
    >
    > SELECT ActionID, AdvisorID, Max(Date) FROM Forecasts GROUP BY ActionID,
    > AdvisorID
    >
    > How can this be achieve and what is my error?
    >
    > Thank you
    >
    > --
    > Vincent
    >

    Jim Michaels Guest

  5. #5

    Default Re: Items with max from another table


    "Jim Michaels" <jmichae3nospam.> wrote in message
    news:aMadnf8eJuVmtmzenZ2dnUVZ_sCdnZ2dcomcast.com. ..
    >
    > "Vincent Lascaux" <nospamnospam.org> wrote in message
    > news:43eed7e4$0$304$626a14cenews.free.fr...
    >> Hello,
    >>
    >> I have 3 tables: Actions, Advisors, Forecasts. Forecasts contains the
    >> fiels ActionID, AdvisorID and Date.
    >> I want to retrieve, for each couple (action, advisor), the max date that
    >> can be found in Forecasts, or NULL if the couple is not present in the
    >> Forecasts table.
    >> I thought I would do it that way:
    >>
    >> SELECT ActionID, AdvisorID, Max(Date) FROM Forecasts RIGHT OUTER JOIN
    >
    > for one thing, Date is a reserved word (a type in fact), so don't use it
    > for a table or column name.
    However, the database will let you do it, I just found out. So much for my
    advice. :-) and surprisingly, concat for a column name even works in a
    SELECT statement, not that it's a convention I am going to stick with - I
    don't need the confusion when I go to read statements 6 months later. :-)
    >
    >> Actions ON Actions.ID = Forecasts.ActionID RIGHT OUTER JOIN Advisors ON
    >> Advisors.ID = Forecasts.AdvisorID GROUP BY ActionID, AdvisorID
    >>
    >> But for some reason that I'm not sure to understand, this seems to return
    >> the same datas as (at least this doesn't return what I want :))
    >>
    >> SELECT ActionID, AdvisorID, Max(Date) FROM Forecasts GROUP BY ActionID,
    >> AdvisorID
    >>
    >> How can this be achieve and what is my error?
    >>
    >> Thank you
    >>
    >> --
    >> Vincent
    >>
    >
    >

    Jim Michaels Guest

Similar Threads

  1. Creating master page where global items arearranged ABOVE local items
    By Alan_Bloom@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 1
    Last Post: October 8th, 02:47 PM
  2. possible for publishers to edit Library Items /recurrent items like navigation?
    By zu in forum Macromedia Contribute General Discussion
    Replies: 1
    Last Post: June 16th, 06:59 PM
  3. possible for publishers to edit Library Items / recurrent items like navigation?
    By cmitchell in forum Macromedia Contribute General Discussion
    Replies: 0
    Last Post: March 5th, 12:50 PM
  4. Table items different from template to actual page
    By shejo webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 10th, 04:33 AM
  5. SELECT to find items NOT common in a table
    By Ivan Demkovitch in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 08:10 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139