mySQL query question

[jdiver]

I have 3 tables I need to get data from, and for the sake of my question I'll show only the relevant fields from each:

users
- user_id
- user_name
- location_id

locations
- location_id
- location_name

orders
- order_id
- user_id
- order_date

What I need to do is get the user_id, user_name, location_name and the order_date of the LAST order the user placed (if any). The query to pull from the users and locations tables is obviously very simple:

SELECT u.user_id, u.user_name, l.location_name 
FROM users u, locations l 
WHERE l.location_id = u.location_id

I'm wondering if it's possible to get the last order_date in the same query.

Currently what I'm doing is running the query to pull the user and location data and then looping through and running a separate query for each user to get the order info, which is killing the processing time. Any suggestions (other than the obvious of storing the last order_date in the users table 😉) would be greatly appreciated. TIA.

[amchargue]

I can't figure this out in my head :eek:

Could you post code?

2 ideas that I can't decide if they would work:

(1)

SELECT u.user_id, u.user_name, l.location_name, max(o.order_date) as maxod
FROM users u, locations l, orders o 
WHERE u.user_id=o.user_id and l.location_id = u.location_id
GROUP BY ???

(2)
do it the other way ... select from users and orders first, then grab the location