MySQL: cross-links

Anon

I have two tables: the first one describes an item, the second one the history of the item. The history table can contain many events linked to an item. Example data set:

ItemID;Date;Version
1;09-14-01;1.0
1;09-15-01;1.1
1;09-16-01;1.2
etc.

What I want to do is create a page with a listing of all the items and also display the latest version number related to the item. So, I need get the latest version number related to the item (could be done using 'ORDER BY Date DESC') from the history table and link it to the item.

I know I could load the data into PHP arrays and do the merging of the data that way, but it just seems like an 'ugly' way of doing it. (no, I'm not saying PHP is ugly. 🙂 it's not.) Any ideas on how I would go about creating a query that would do this already on the MySQL side?

Thanks.

lawnmowerman

If I understand your request, you want to be able to pull data out of 2 MySQL tables at once and store them in a single result set.

Assuming your 2 tables are "item" and "history", what you can do is:

$sql = "SELECT item.ItemID, item.Name, item.Descr, history.Date, history.Version FROM item, history WHERE itemID = 1 AND history.ItemID = item.ItemID";

$result = mysql_query( $sql );
while( $row = mysql_fetch_array( $result )) {
<do stuff with the results>
}
The $row array will contain:
$row[item.ItemID], $row[item.Name], $row[item.Descr], $row[history.Date], and $row[history.Version].

-Rich

Anon

Thanks, I've gotten that far myself. The thing is, I only want the latest history item related to the item, not all of them. That's what I have trouble factoring in to the query.

phigga

i ran into this about a week ago, so i can sympathize :-)

what you want to do is add to the end of your sql line "ORDER BY <date> LIMIT 1". this will order all the "histories" by date, and limit what it returns to the first entry of the ordered list.

-phil

Anon

If only it was that easy. 😉 The thing is, if I use LIMIT just like that in the query, I won't get more than one of the 'item' table entries, either.

The idea is to create a list like this:

IID; ItemName; Version
100; Alpha; 1.0
101; Beta; 1.2
102; Gamma; 4.3
etc.

phigga

ahhh...i get it.

i think this can be done with sub-queries. now here's the bad news....subqueries aren't legal in MySQL (AFAIK). so i don't think you can do this with one single SQL command.

if you do figure this out, please post a reply to this. i have a project that's very similar to this, and i'd like to know what you come up with.

zkent

Couldn't you use GREATEST(item.version) in your SELECT and use GROUP BY item to get each items greates stored value in the version field? I wish I had time to test this out....

phigga

GREATEST() is used to compare a group of values....how would you go about grabbing all the versions for a given item, so that they could be compared?

to answer your question, i don't think GREATEST() is the answer here. I don't really think this can be done in one statement.

howler

Actually, Zach's posting put me on the right track; a little bit of RTFM and I came up with the following statement:

SELECT history.IID, item.Name, MAX(history.Version) AS Version FROM history,item WHERE item.ID = history.IID GROUP BY history.IID;

With the limited set of data I had, it seems to work great. Hope this helps you.

zkent

I have discovered that, with a lot of patience, and the proverbial RTFM, you can do just about anything with ONE query! I am just to lazy (or behind schedule) most of the time to do it that way. When I am being paid hourly, my queries are AWESOME! Flat-rate.... why use one when five will do....