SQL query question

[pallan]

I have been using SQL for quite some time with mySQL, but there is a query I have not been able to bend my brain around.

If I have 2 tables say inventory (inv) and inventory transactions (intran) how would I construct a query to show me the most recent transaction for every item in inventory, BUT (this is part I don't get) what if some of the inventory items have never had a transaction and I want my query to show that?

I have been avoiding the need for this in my PHP programs, but now I need to do it. Any help would be greatly appreciated.

Thanks

Peer

[Anon]

Try using a MAX() on the date, and merge the two tables with OUTER JOIN.

[sepodati]

mysql> select * from inv;
+------+--------------+
| id | name |
+------+--------------+
| 1 | Hammer |
| 2 | Toilet Bowl |
| 3 | Pencil |
| 4 | Dog |
| 5 | Bill Clinton |
+------+--------------+
5 rows in set (0.00 sec)

mysql> select * from intran;
+----+----------------+
| id | tran |
+----+----------------+
| 1 | 20010125161008 |
| 1 | 20010126161023 |
| 2 | 20010122161038 |
| 5 | 20001211161055 |
| 5 | 20001027161230 |
+----+----------------+
5 rows in set (0.00 sec)

mysql> select inv.id,inv.name,ifnull(max(intran.tran),'No Transaction') from inv
left join intran on inv.id = intran.id group by inv.id;
+------+--------------+-------------------------------------------+
| id | name | ifnull(max(intran.tran),'No Transaction') |
+------+--------------+-------------------------------------------+
| 1 | Hammer | 20010126161023 |
| 2 | Toilet Bowl | 20010122161038 |
| 3 | Pencil | No Transaction |
| 4 | Dog | No Transaction |
| 5 | Bill Clinton | 20001211161055 |
+------+--------------+-------------------------------------------+
5 rows in set (0.06 sec)

Adapt to your needs...

---John Holmes...

[sepodati]

You can do this, too....

mysql> select inv.id,inv.name,ifnull(date_format(max(intran.tran),'%a %b %D %l:%
i:%s %p'),'No Transaction') as last_tran from inv left join intran on inv.id = i
ntran.id group by inv.id;
+------+--------------+-------------------------+
| id | name | last_tran |
+------+--------------+-------------------------+
| 1 | Hammer | Fri Jan 26th 4:10:23 PM |
| 2 | Toilet Bowl | Mon Jan 22nd 4:10:38 PM |
| 3 | Pencil | No Transaction |
| 4 | Dog | No Transaction |
| 5 | Bill Clinton | Mon Dec 11th 4:10:55 PM |
+------+--------------+-------------------------+
5 rows in set (0.06 sec)

---John Holmes...

[sepodati]

I forgot to throw a %Y in there for year...oops...