Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2007
    Posts
    77

    Unanswered: Getting the maximum value in a join and displaying the result as one

    Hi to all,

    This is a beginner question and all help and advice would be greatly appreciated.

    I am trying to join 2 tables and is having difficulty on trying to show a list of items in inventory.

    Every item has a record in the "inventory" table and accompanying details about that item, which is stored in the "inventory_details" table. Every now and then, I update the details of a certain item and inserts that update detail on the "inventory details" table.

    In short the "inventory" table acts as the "master" table and the "inventory_details" table acts as the "details" table.

    The problem is that I want to view the latest info about a certain item and along with that info is the latest detail of that item. I only know that this could be done by a JOIN and tried doing one but the result has multiplied.

    SAMPLE DATA:
    Code:
    "inventory" table:
    
    inv_id		item_name	item_property_code	item_serial_number
    -----------------------------------------------------------
    23		Item P		prop-P			serial-P
    32		Item A		prop-A			serial-A
    94		Item X		prop-X			serial-X
    
    
    "inventory_details" table:
    
    inv_details_id		inv_id		remarks		school_year
    ----------------------------------------------------------
    1			32		good		2002
    2			94		good		2001
    3			32		good		2003
    4			94		good		2002
    5			23		transferred	2005
    6			94		lost		2003
    7			32		good		2004
    8			32		broken		2007
    When I try to join the 2 tables, I get 8 records all in all. I was hoping to see only 3 records because there are 3 records on the "inventory" table. I just wanted to include the latest record which holds the "school_year" and "remarks" field of those 3 items by basing on the "school_year" field.


    What I did was include a MAX(school_year) on my query and used a GROUP clause, by grouping it on the "inv_id" field.

    My query:
    Code:
    SELECT inventory.inv_id, item_name, item_property_code, item_serial_number, remarks, MAX(school_year) FROM
    inventory INNER JOIN inventory_details ON inventory.inv_id = inventory_details.inv_id
    GROUP BY inventory.inv_id
    ORDER BY inventory.inv_id;
    RESULTS: (item_property_code and item_serial_number were not omitted)
    Code:
    inv_id		item_name	remarks		max(school_year)
    -----------------------------------------------
    23		item P		transferred		2005
    32		item A		good			2007
    94		item X		good			2003
    The "school_year" field display is correct but the "remarks" does not match the exact record in the "inventory_details" table. "item A" remark's must be "broken" instead of "good", and "item X" must be "lost" instead of "good".

    If this is the case, should I retrieve the "inv_details_id" (which is the PK of table "inventory_details") , so that I can get the correct "remarks" field and then include it in my JOIN query? A sub-query would be appropriated for this problem?


    many thanks...

    by the way, I am using MySQL 5.0 as my DBMS and here is the table definition:

    1.) Table "inventory"
    Fields:
    inv_id - Primary Key
    item_name - varchar
    item_property_code - varchar
    item_serial_number - varchar

    2.) Table "inventory_details"
    Fields:
    inv_details_id - Primary Key
    inv_id - Foreign Key of "inventory" table
    remarks - varchar
    school_year - year[/HTML]
    Last edited by r937; 01-25-09 at 14:45.
    Programming is fun!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Panoy
    What I did was include a MAX(school_year) on my query and used a GROUP clause, by grouping it on the "inv_id" field....

    The "school_year" field display is correct but the "remarks" does not match the exact record in the "inventory_details" table.
    i hope you don't mind i edited your post to make it more readable

    the query you wrote would not have even executed in any other database system besides mysql, because of a syntax error

    when the SELECT clause contains both aggregate expressions, like MAX(something), as well as non-aggregate columns, then according to standard SQL, every non-aggregate column must also be in the GROUP BY clause

    mysql, as you might or might not know, has relaxed this requirement, and allows "hidden" columns in the SELECT clause -- they are called hidden because they are not mentioned in the GROUP BY clause

    however, this can be problematic...
    Do not use this feature if the columns you omit from the GROUP BY part are not constant in the group. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

    -- GROUP BY and HAVING with Hidden Columns
    this is what caused your incorrect reasults, since there are multiple detail rows for each inv_id, and they have different values in the remarks column

    what you want is to find, for each inv_id, the row that has the maximum year, and you can get this by joining the maximum value back to the details table to match that row

    this is described here: The Rows Holding the Group-wise Maximum of a Certain Field

    in your case,
    Code:
    SELECT inventory.inv_id
         , inventory.item_name
         , inventory.item_property_code
         , inventory.item_serial_number
         , inventory_details.remarks
         , inventory_details.school_year 
      FROM inventory 
    INNER 
      JOIN ( SELECT inv_id
                  , MAX(school_year) AS max_year
              FROM inventory_details 
             GROUP
                 BY inv_id ) AS inv_maxes
        ON inv_maxes.inv_id = inventory.inv_id
    INNER
      JOIN inventory_details
        ON inventory_details.inv_id = inv_maxes.inv_id
       AND inventory_details.school_year = inv_maxes.max_year
    the subquery acts like a separate table, and in fact is called a derived table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    77
    thanksk for the reply

    I was just also reading the MySQL manual just before starting this thread at this forum. I have yet to try out your solution, but hey, thanks and it made it clearer for me.

    I'll try my best to find out, if there are also other solutions available aside from yours.

    by the way, the formatting of your query looks pretty good.

    many thanks
    Programming is fun!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Panoy
    I'll try my best to find out, if there are also other solutions available aside from yours.
    there is another solution, involving a correlated subquery
    Code:
    SELECT inventory.inv_id
         , inventory.item_name
         , inventory.item_property_code
         , inventory.item_serial_number
         , inventory_details.remarks
         , inventory_details.school_year 
      FROM inventory 
    INNER
      JOIN inventory_details
        ON inventory_details.inv_id = inventory.inv_id
       AND inventory_details.school_year = 
           ( SELECT MAX(school_year)
               FROM inventory_details 
              WHERE inv_id = inventory.inv_id )
    but the derived table is usually more efficient than the correlated subquery

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Posts
    77
    wow that is new..

    what is the difference between the previous one? I really don't know about correlated subquery and derived tables.

    btw, thanks for the first solution you gave me. It helped me alot, I didn't know that there was this "derived" tables kind of thing.

    I will try my best to know more about this. Many thanks!
    Programming is fun!

  6. #6
    Join Date
    Mar 2007
    Posts
    77

    is it safe to use GROUP BY now?

    hi to all,

    it's been a while since I replied to this post of mine.

    thanks to r937.

    I just went back to this problem a while ago, and I want to ask if it is safe to use a GROUP BY on this query? I have tried using the 2 queries, they all work fine but they all display each detail for the item.

    Can I use the GROUP BY statements, so that I can only display each single item along with its maximum year? Even if there are a number of details related to it?
    Programming is fun!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Panoy
    Can I use the GROUP BY statements, so that I can only display each single item along with its maximum year?
    no

    ("these aren't the droids you're looking for")

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Posts
    77
    Ok.

    Your query is just right. But if an item that has many details on it, and some of those records have the same school year value, then the whole query you gave to me will show multiple records of that item.

    Using GROUP BY is not appropriate for this query, then that means that I have to make good of assuming that the user will not make a mistake of entering the same school year value that is already in the details records of that item.

    pardon me, if it sounds like that I am really asking for the right answer. No, its not that way. I appreciate the answers you have shared.

    many thanks!
    Programming is fun!

  9. #9
    Join Date
    Jul 2009
    Posts
    17
    SELECT inventory.inv_id
    , inventory.item_name
    , inventory.item_property_code
    , inventory.item_serial_number
    , inventory_maxes.remarks
    , inventory_maxes.school_year
    FROM inventory
    INNER
    JOIN
    ( SELECT inv_id,remarks
    , MAX(school_year) AS max_year
    FROM inventory_details
    GROUP BY inv_id ,remarks) AS inv_maxes
    ON inv_maxes.inv_id = inventory.inv_id


    hai i m just trying ...If i am wrong just let me know..please..

    Thanks in Advance !

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Vaibhav Vyas
    hai i m just trying ...If i am wrong just let me know..please..
    u r wrong

    also, in responding to threads that were solved months ago... forums: ur doing it wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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