Two Joins?

[jchastai]

This would seem fairly basic, but it has been a long day.

I two fields in one table called owner and submitter. I have another table called users with a field called ID.

Now both the owner and submitter field can (don't have to) contain users.ID values, but they may not be the same users.ID value.

There for I need to do something like

SELECT ...
FROM Table1 LEFT JOIN Users ON Table1.Owner = Users.ID
- OR -
Table1 LEFT JOIN Users ON Table1.Submitter = Users.ID

This would seem like a fairly basic request, but I am not getting it to work.

Any help would be appreciated.
-- Jeff Chastain

 

[JonathanC]

Heres a good example...

Table Employee has ManagerID and EmployeeID,
you want to know the manager of an employee with employeeid 12...

you should say:

Select Manager.Name
from Manager Employee
inner join
Employee Emp on
Emp.ManagerID = Manager.EmployeeID

 

[JonathanC]

...correction


Select Manager.Name
from Employee Manager
inner join
Employee Slave on
Slave.ManagerID = Manager.EmployeeID

 

[tlbroadbent]

Try this.

SELECT <Select List>
FROM Table1

-- Use Inner Join to return only matching rows
INNER JOIN Users ON Table1.Owner = Users.ID

-- Remove Where clause if not needed
WHERE isnumeric(Table.Owner)=1

-- Remove ALL to eliminate duplicate rows
UNION ALL

SELECT <Select List>
FROM Table1

-- Use Inner Join to return only matching rows
INNER JOIN Users ON Table1.Submitter = Users.ID

-- Remove Where clause if not needed
WHERE isnumeric(Table.Submitter)=1
Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm