Lee Clements
3/20/2007 4:44:00 PM
Thanks for the swift response Roy!
The assistant was ommitted by mistake but following the alias method you
demonstrated below I was able to add it back in and get both the owner and
the assistant displaying as I wanted.
I couldnt find an example in any of my books where this was being done.
Fantastic.
Thanks again,
Lee
"Roy Harvey" <roy_harvey@snet.net> wrote in message
news:vv2003l3qominpap7ov0vs6dn86ndso05f@4ax.com...
> Your problem is that the query, as written, expects the same row in
> tblStaff to be BOTH the owner AND the assistant. Which seems
> unlikely.
>
> The answer is to join to tblStaff twice, once for the owner and again
> for the assistant. Since there are two instances of the same table
> aliases are assigned and used to identify which one is intended. See
> if this gives you something to start with.
>
> SELECT tblJobs.job_id, tblJobs.job_date,
> O.staff_name AS owner,
> tblJobs.job_site, tblJobs.job_car, tblJobs.job_notes
> FROM tblJobs
> JOIN tblStaff as O
> ON tblJobs.job_owner = O.staff_id
> JOIN tblStaff as A
> ON tblJobs.job_assistant = A.staff_id
>
> I noticed that there was no reference in the output data to the
> assistant, but perhaps that was intentional. As it stands, if there
> are multiple assistants the query above will return multiple rows for
> the same owner. Or perhaps you have no need for assistant at all,
> which case you probably only needed to remove the test " AND
> tblJobs.job_assistant = tblStaff.staff_id" from your original query.
>
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 20 Mar 2007 16:16:53 -0000, "Lee Clements"
> <lee.clements.nospam@btopenworld.com> wrote:
>
>>oops, my misake hit the send key by mistake, the missing text is:-
>>
>>[tblJobs]
>>job_id (PK, int)
>>job_date (datetime)
>>job_owner (FK, int)
>>job_site (FK, int)
>>job_car (FK, int)
>>job_assistant (FK, int)
>>job_notes (varchar)
>>
>>[tblStaff]
>>staff_id (PK, int)
>>staff_name (varchar)
>>staff_dept (FK, int)
>>staff......
>>...
>>
>>Both the job_owner and job_assistant are FK's to the tblStaff table but
>>when
>>i try and do a join to print the staff_name rather than their id I don't
>>get
>>any results, my query is (via designer)
>>
>>SELECT tblJobs.job_id, tblJobs.job_date, tblStaff.staff_name AS owner,
>>tblJobs.job_site, tblJobs.job_car, tblJobs.job_notes
>>FROM tblJobs INNER JOIN tblStaff ON tblJobs.job_owner =
>>tblStaff.staff_id AND tblJobs.job_assistant = tblStaff.staff_id
>>
>>How do I create a query that can display the staff_name for job_owner and
>>job_assistant? I have been playing with this for hours changing indexes
>>and
>>keys etc, and not getting anywhere - I am obviously looking at this from
>>the
>>wrong angle, guidance and advice would be appreciated.
>>
>>Thanks in advance
>>Lee
>>
>>"Immy" <therealasianbabe@hotmail.com> wrote in message
>>news:OVTZ$mwaHHA.348@TK2MSFTNGP02.phx.gbl...
>>> Lee - there are appears to be no data in your table! ;-)
>>>
>>>
>>> "Lee Clements" <lee.clements.nospam@btopenworld.com> wrote in message
>>> news:uirKqjwaHHA.4396@TK2MSFTNGP06.phx.gbl...
>>>>I am not sure if this is the right forum but I am having problems doing
>>>>what I thought would be a simple query, my table looks like this
>>>>
>>>>
>>>
>>>
>>