Please help with a SELECT statement

[accent]

I have a users table and a orders table.  I need to display the all the users on the page with the latest order for each user.

Can I do that in one Select statement?

 

I tried the following but it will return all the orders. I only need one order for one user.

SELECT orders.orderid, users.username FROM orders, users WHERE orders.userid=users.id ORDER BY orders.datetime DESC;

 

Any ideas?

 

Thanks a lot!

 

[smerny]

you need to join the tables

 

join syntax here - http://dev.mysql.com/doc/refman/5.0/en/join.html

 

instead of "FROM orders, users" you need to have a join, like "FROM orders INNER JOIN users ON orders.orderid"

[dreamwest]

SELECT * FROM orders as o, users as u WHERE o.userid=u.id ORDER BY o.datetime DESC;

 

 

[kickstart]

Hi

 

Assuming that the orderid is always ascending (so the later the order the higher the order id):-

 

SELECT *
FROM (SELECT a.userid, MAX(a.orderid) FROM orders a GROUP BY a.userid) LatestOrders
INNER JOIN Orders b ON LatestOrders.orderid = b.orderid
INNER JOIN Users c ON LatestOrders.userid = c.id

 

Narrow the SELECT * down to the particular fields you are interested in.

 

All the best

 

Keith