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.
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
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.
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)
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?
by the way, I am using MySQL 5.0 as my DBMS and here is the table definition:
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.
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?
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.
( SELECT inv_id,remarks
, MAX(school_year) AS max_year
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..