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

Panoy · #1 (**permalink**) 01-25-09, 11:06

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]

r937 · #2 (**permalink**) 01-25-09, 14:06

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...

Quote:

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

Panoy · #3 (**permalink**) 01-25-09, 22:05

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

r937 · #4 (**permalink**) 01-25-09, 22:11

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

Panoy · #5 (**permalink**) 01-26-09, 01:13

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!

Panoy · #6 (**permalink**) 02-25-09, 05:13

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?

r937 · #7 (**permalink**) 02-25-09, 08:42

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")

Panoy · #8 (**permalink**) 02-25-09, 22:40

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!

Vaibhav Vyas · #9 (**permalink**) 07-01-09, 10:07

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 !