Re: normalization - two linked fields in one table

"Trooper" <andrew_zx5_at_NOSPAMyahoo.com> wrote in 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
> ====
> TaskID
> assigned_to
> poster_id
>
>
> User
> ====
> UserID
> name
>
>
> assigned_to and poster_id are linked to the UserID. What this means
> is that it
> should be keeping track of who posted the task and who it's currently
> assigned
> to.
>
> This works in my head, but then I try to write a query and can't
> figure out how
> to pull a name from the User table for each of the two fields in the
> Task table.
>
> If it matters, I'm using an MS Access database (I told you it was
> small-time),
> but I could just as well use mySQL if I have to.

You have to pull in the User table twice. You can do this in the graphical query definition of Access.

In SQL you'll have to specify an alias for at least one of the occurrences of User in the from clause (best would be for both). Then you qualify all columns that come from the user table with the respective alias (e.g. poster.name) and probably also specify a column alias (poster.name as posterName).

hth,
Heinz Received on Mon Nov 11 2002 - 10:52:44 CET