Hi,

It was a bit difficult to understand exactly what your requirements are,
but I think you might want to consider useing the (non SQL) concecpt of
roles. You database contains people. Some of them are employees, some
are assitants, some are bosses, some are consultants, ...

You would have one table of people, e.g. tbl_person.

Then you would have one table per role, e.g. tbl_employee,
tbl_assistant, etc.

If a person is an employee then you have one row in tbl_person and one
in tbl_employee. If that person is ALSO a boss, you would also have a
row in tbl_boss. All these would have person_id as primary key.

The good thing with this is that it is easy and intuitive to figure out
how to setup your relations. An employee would have an association to a
boss, so tbl_employee would have a field boss_id referencing
tbl_boss(person_id).

Also, if a certain role implies certain attributes or other relations,
those would be stored in that role's table. For example, all employees
would probably have an employee number and belong to a department. So,
tbl_employee would have the columns emp_no and dep_id.

The downside is that it requires a lot of joins. But you can always
"de-normalize", merging the role tables into tbl_person. But
conceptually, you would probably find it helpful to think of it in terms
of these "role tables".

Kjell

dinol.softedge wrote:

>
>
> Hi
>
> I am trying to do a join from table 1 (called tbl_job_card) to table 2
> (called tbl_employee). Both tables have a field called employee_id
> that links them in a one-to-many relationship. Now I have to add a
> separate field called assistant_id to also link to employee_id from
> tbl_employee. Therefore both employee_id and assistant_id link to
> employee_id from tbl_employee. The reason for this is that an employee
> can be the main emmployee or an assistant on a job. I give the
> employee table an alias of tbl_assistant. Whenever I run the query,
> however, it pulls up the same employee for both employee and
> assistant. Is there another way to do this query? Thanks
>
>

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64