mySQL Foreign Keys

Kold

Hello!

I have two tables that i want to ling together;

#1
User ID (primary)
User Cost

#2
Task
Task User

There is a 1 to many relationship between #1 and #2. There can be many different records for 1 User ID in #2.

e.g.
Task1, User 3
Task 2, User 3

I would like to link the 'User ID' to 'Task User' so that when i searched for 'Task 1' for instance, i would be able to select the SUM() of 'User Cost' which would allow me to add all of the costs up for any users who has 'Task 1'

I have only ever used foreign fields in access (drag and drop!). I have looked at the mySQL documentation but am not 100% sure what it is all about.

Is this possible? (i.e. "SELECT SUM(User Cost) WHERE FORIEGN_KEY(Task) = "1")

Can anyone give me any help/guidance regarding creating and then using them?
Thanks for any info!

Roger_Ramjet

Foriegn keys are used to join tables in queries. Join

In your case your explanation indicates that you have a many-to-many relationship between users and tasks: "for any users who has 'Task 1'
".

You may be saying that table #2 is a matrix that links to a further tasks table, in which case your design works so long as you know how to use it.

If it just users and tasks, 2 tables, then you have to change your design: create a table that breaks the relationship down into 2 one-to-many relationships.

Technically, you should do this through 2 sunsiduary tables; each resolves one side of the many-to-many relationship. The 2 tables are then joined in a one-to-one relationship through multi-column indexes.

I just over-lay the 2 into 1; AND REMEMBER THE LIMITATIONS. You cannot go from multiple users to multiple tasks. You cannot join all 3 tables in the same query. If you do then calculated or aggregated fields will be dodgy at best.

If you want all user costs for a task then you join the matrix to the users table and pass them the task id. Likewise for all tasks a user has. Nested queries or temp tables are used for aggregate functions, summary reports, etc.

marian

is this what you meant?

table1
User ID (primary)
User Cost

table2
Task
Task User ==>just use varchar 1 (when inserting into this table grab the userid from table1 by using mysql_insert_id())

so table1 and table2 looks like this

table1
id cost
1 5
2 7
3 6

table2
task user
test 1
test2 1
ttt 2

now to get the sum for userid 1 just do this:

select cost as ct, count(*) from table1, table2 where table1.id=table2.user

now to do the sum just multiply number of times user appears in table2 by the cost, and you are done.

check the mysql above, it was just a rough draft.

goodluck

Kold

Hello.

The tables are one to many. There is only one 'User ID' (auto increment), but each user ID is used multiple times in my 'Task' table. Is this correct?

Here is what i am trying to do using PHP/mySQL, the long way round, perhaps this will help explain better.

$TaskSQL = "SELECT `Task_User` FROM tblTask WHERE Task = '2'", $db);

If($TaskRes = mysql_fetch_array($TaskSQL)){

  //Loop through all tasks.
  Do{

  $UserSQL = mysql_query("SELECT `User_Cost`FROM tblUsers WHERE User_ID = '".$TaskRes['Task_User']."'", $db);

 $TotalCount += mysql_fetch_array($UserSQL);

  }While($TaskRes = mysql_fetch_array($TaskSQL));
}

echo $TotalCount; //Everything added up...

This will work, however with a few hundred thousand records possible in the tasks table, this could go a little bit on the resource hog side, especialy as it will probably be placed in the header!

Thanks for your help!

Roger_Ramjet

AND EACH TASK HAS MULTIPLE USERS, SO IT IS MANY-TO-MANY

Roger_Ramjet

What you end up with is multiple queries where 1 will do.

$query = "SELECT sum(tblUsers.User_Cost) as total FROM tblUsers JOIN tblTask ON tblUsers.User_id = tblTask.Task_User WHERE tblTask.Task = 2";

Will return 1 row with 1 field = the total cost.