One Table with 2 (FK) to One Table with (1) PK

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

One Table with 2 (FK) to One Table with (1) PK

Author

Topic

jacisme
Starting Member

34 Posts

Posted - 2003-05-18 : 14:03:12

This should be an easy one for you guys...;)
Thank you for your help...

My problem is that EmployeeID from the Employee Table is referenced to (two) columns on the MainOrders table (SalespersonID and EntrypersonID)

SELECT MainOrders.SalesPersonID, MainOrders.EntrypersonID, EmployeeList.EmployeeFirst
FROM EmployeeList INNER JOIN MainOrders ON (EmployeeList.EmployeeID = MainOrders.EntrypersonID) AND (EmployeeList.EmployeeID = MainOrders.SalesPersonID)

The above query gives no results. I am thinking I need to do a subquery, but I have tried many with in many different directions, all results are none to incomplete. All I need is both names of the employees, respective to each row.

------------------------------------------------
**MainOrders(Table)**
SalespersonID(Column)FK to EmployeeID(PK)
EntrypersonID(Column)FK to EmployeeID(PK)
------------------------------------------------
**EmployeeList(Table)**
EmployeeID(Column)PK
EmployeeFirst(Column)
EmployeeLast(Column)
------------------------------------------------

_/AC

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-18 : 15:22:08

SELECT MainOrders.SalesPersonID, MainOrders.EntrypersonID, EmployeeList.EmployeeFirst
FROM EmployeeList
INNER JOIN MainOrders
ON EmployeeList.EmployeeID = MainOrders.EntrypersonID
or EmployeeList.EmployeeID = MainOrders.SalesPersonID

will give you the 2 records

SELECT m.SalesPersonID, m.EntrypersonID, e1.EmployeeFirst
, e2.EmployeeFirst
FROM MainOrders m
INNER JOIN EmployeeList e1
ON e1.EmployeeID = m.EntrypersonID
INNER JOIN EmployeeList e2
or e2.EmployeeID = m.SalesPersonID

will give them in a single row

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jacisme
Starting Member

34 Posts

Posted - 2003-05-18 : 15:43:55

Thanks, but I am getting a (Missing Syntax) error. Any thoughts.....

Thanks again,

JAC

Edited by - JACisMe on 05/18/2003 15:44:40

jacisme
Starting Member

34 Posts

Posted - 2003-05-18 : 15:45:17

quote:

Thanks, but I am getting a (Missing Syntax) error (in the Joins). Any thoughts.....

Thanks again,

JAC

Edited by - JACisMe on 05/18/2003 15:44:40

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-18 : 16:13:01

Second one has an or instead of on but the first looks ok.
Post the whole error message if it's still happenning.

SELECT m.SalesPersonID, m.EntrypersonID, e1.EmployeeFirst, e2.EmployeeFirst
FROM MainOrders m
INNER JOIN EmployeeList e1
ON e1.EmployeeID = m.EntrypersonID
INNER JOIN EmployeeList e2
ON e2.EmployeeID = m.SalesPersonID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jacisme
Starting Member

34 Posts

Posted - 2003-05-18 : 16:22:40

Syntax error (missing operator)in query expression 'e1.EmployeeID = m.EntrypersonID
INNER JOIN EmployeeList e2 ON e2.EmployeeID = m.SalesPersonID'.

Above is the error, everything to me seems okay, can't figure it out.
Thanks for your help...

JAC

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-18 : 16:49:15

looks like it hasn't parsed the query properly. Try typing it in again or adding a few extra spaces as delimitters.

You are using query analyser for this aren't you?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jacisme
Starting Member

34 Posts

Posted - 2003-05-18 : 17:05:06

I retyped it, same error message though. I am using Access 2000.

Thanks,
JAC

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-18 : 17:10:43

something like

SELECT m.SalesPersonID, m.EntrypersonID, e1.EmployeeFirst, e2.EmployeeFirst
FROM (MainOrders m
INNER JOIN EmployeeList e1
ON e1.EmployeeID = m.EntrypersonID)
INNER JOIN EmployeeList e2
ON e2.EmployeeID = m.SalesPersonID

(there's an access section here too)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 05/18/2003 17:12:46

jacisme
Starting Member

34 Posts

Posted - 2003-05-18 : 17:12:30

YES!!!!!!

You did it!!!!!
Thank you for help...

Jason

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources