Join Query Help

[freshwebs]

This is probably a really simple query, but I'm having trouble figuring it out.

 

I have two tables.  One has user account information and the second has profile information.  Changes to the profile are logged, so every time someone saves their profile, another row is added to the profiles table with the profile data, user_id, and date.

 

I'm trying to write a query to join the two tables, only using the most recent profile data for each user.  So, the query will return one row for every user with all their info.  How would this be done?

 

Thanks in advance!

[br0ken]

You would need to show us your table layouts for us to help you with one.

[freshwebs]

Users: id, first_name, last_name, email, password

Profiles: id, user_id, date, address, city, state, zip, etc.

 

Every time the user changes their profile, another row is added with their user_id and the date.  The query needs to select their row from the users table and their latest profile record.

 

Thanks!

[br0ken]

Give this a try. Remember to set $userID to the userID you're trying to request.

 

SELECT u.first_name, u.last_name, u.email, u.password, p.address, p.city, p.state, p.zip FROM tblUser u, tblProfile p WHERE u.id = $userID AND u.id = p.user_id ORDER BY p.date DESC LIMIT 1

[freshwebs]

Sorry, I meant to say that I need to select all the users and their most recent profile.

[br0ken]

Ah ok. Then try this one...

 

SELECT u.first_name, u.last_name, u.email, u.password, p.address, p.city, p.state, p.zip FROM tblUser u, tblProfile p WHERE u.id = p.user_id  AND p.date = (SELECT MAX(p2.date) FROM tblProfile p2 WHERE p2.user_id = u.id) ORDER BY p.date DESC LIMIT 1

 

There is probably a better way to do this but without actually being able to test it this is all I can provide. This will return all user/profile relations and then discard all but the one that has the highest date (ie the most recent one).

 

Can I ask, how come you create a new profile each time the user updates it? It seems highly inefficient to me. I imagine you have a reason for keeping their old details on file?

[freshwebs]

Thanks, that gets the job done.  I think there might be a more efficient way to do it, but it's sufficient for now.

 

This is a really dumbed down version of what I'm doing.  Basically, we need to be able view what someone's profile looked like any given week.  I have it setup so that at max one new profile record is created each week, otherwise it's just updated.

 

Let me know if you, or anyone else, has any improvements on the last query.

 

 

[fenway]

Run the EXPLAIN... I think you'll see a dependent subquery, which isn't optimal