Re: normalization - two linked fields in one table

"Trooper" <andrew_zx5_at_NOSPAMyahoo.com> wrote in message news:<JhIz9.796567$Ag2.27051080_at_news2.calgary.shaw.ca>...
> I'm setting up a small web based program to help manage "Tasks" between
> family members. Kind of like Bugzilla, but on a way smaller scale.
> My problem is that a "Task" has two fields in it that relate to the same
> table:
>
> Task User
> ==== ====
> TaskID UserID
> assigned_to name
> poster_id
>
> assigned_to and poster_id are linked to the UserID.
<...>
> can't figure out how
> to pull a name from the User table for each of the two fields in the Task
> table.
>
You need to join the user table twice, once for assignee, then for poster:

SELECT t.TaskID, a.name as Assignee, p.name as Poster FROM (Task as t INNER JOIN User as a ON t.assigned_to=a.UserID)

                INNER JOIN User as p ON t.poster_id=a.UserID