UPDATE using Multiple Linked Tables

[master82]

I have these3 tables: Users, Job and JobPosition

Job contains: (Dept.)
jID (unique identifier)

Job Position contains:(Job positions in dept)
jpID (unique identifier)
JID (linked to Job table to identify which positions belong in what dept.)
RATE

Users table contains: (List of employees)
UserID (unique identifier)
jID (linked to Job table to establish users dept.)
jpID (linked to Job Position table to establish the users job role)
PROGRESS

Ok, what I want to do is a simple UPDATE query that will update PROGRESS in the users table by adding the current PROGRESS value to RATE found in the Job Position table (PROGRESS + RATE).
What makes it too complicated for me is that i think you have to use JOIN etc in the query to correctly select the rate from the correct position and dept - confusing stuff!

Anyone confiedent enought on using JOIN etc... in these queries that can help?

[master82]

bump...

I think it goes something like

[code]
<?php

"UPDATE users u LEFT JOIN jobposition us ON u.jpid=us.jpid SET us.userid=us.userid WHERE u.userid=$userid";

?>
[/code]

Anyone able to do this kind of thing?

[btherl]

What about this?

[code=php:0]UPDATE users SET progress = progress + rate FROM jobposition WHERE jobposition.jpid = users.jpid[/code]


I'm not 100% sure it will work, but it's worth a try :)

[master82]

Nice try but that didnt work ???

Im pretty sure that the WHERE part needs to concern the userid