One Primary Key to Two Column in same table

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

General SQL Server Forums

New to SQL Server Programming

One Primary Key to Two Column in same table

Author

Topic

triggydj
Starting Member

4 Posts

Posted - 2015-02-09 : 10:55:22

i have a table name Dispatch with the following column
ID,Date, Name, Pickup, Going

i have a next table called Move with the following column
ID, MoveName

i link Move table with Dispatch table via relationship.
ID_Pickup, ID_Going

upon running query no data is shown unless i remove one of the column form the query either pickup or going

d.wint

jleitao
Posting Yak Master

100 Posts

Posted - 2015-02-09 : 11:09:33

can you post your query?

------------------------
PS - Sorry my bad english

triggydj
Starting Member

4 Posts

Posted - 2015-02-09 : 11:35:54

SELECT tblTripInfo.*
FROM tblDriverName INNER JOIN
tblTripInfo ON tblDriverName.DRIVERID = tblTripInfo.DRIVERNAME INNER JOIN
tblMove ON tblTripInfo.PICKUP = tblMove.MOVEID AND tblTripInfo.GOING = tblMove.MOVEID INNER JOIN
tblSizeType ON tblTripInfo.SIZETYPE = tblSizeType.SIZEID

d.wint

jleitao
Posting Yak Master

100 Posts

Posted - 2015-02-09 : 11:50:44

not sure what you really have in your tables, but i think your problem is in the inner join with tblMove table:
INNER JOIN tblMove
ON tblTripInfo.PICKUP = tblMove.MOVEID
AND tblTripInfo.GOING = tblMove.MOVEID

you are "saying" that in the same line in tblTripInfo table your columns PICKUP and GOING are equals and you have a match record (MOVEID) on tblMove table. are that right?

can you post a few lines of each table, as example, and the output you are expected?

------------------------
PS - Sorry my bad english

triggydj
Starting Member

4 Posts

Posted - 2015-02-09 : 14:42:54

jleitao
Posting Yak Master

100 Posts

Posted - 2015-02-09 : 14:57:38

not sure what you need, however i think you need 2 joins with tblMove

SELECT tblTripInfo.*,
PM.MOVE as PICKUP_DESCRIPTION
GM.MOVE as GOING_DESCRIPTION
FROM tblDriverName
INNER JOIN tblTripInfo
ON tblDriverName.DRIVERID = tblTripInfo.DRIVERNAME
INNER JOIN tblMove as PM -- to get the PICKUP "detail"
ON tblTripInfo.PICKUP = PM.MOVEID
INNER JOIN tblMove as GM -- to get the Going "detail"
ON tblTripInfo.GOING = GM.MOVEID
INNER JOIN tblSizeType
ON tblTripInfo.SIZETYPE = tblSizeType.SIZEID

if doesn't work post a sample of your expected result

------------------------
PS - Sorry my bad english

triggydj
Starting Member

4 Posts

Posted - 2015-02-09 : 15:22:14

tblMove
ID, Move
1, Base
2, Freezone
3, Wharves

tblTripinfo
ID, TripNo, Name, CNo, ChasNo, Pickup, Going
1, 25 , Alex, 12 , 335R , 1 , 2
2, 26 , Mark, 38 , 476R , 3 , 1
3, 35 , Stacy, 01 , 368 , 3 , 2

my result should be
ID Trip, Name, CNo, ChasNo, Pickup, Going
1, 25 , Alex, 12 , 335R , Base , Freezone
2, 26 , Mark, 38 , 476R , Wharves , Base
3, 35 , Stacy, 01 , 368 , Wharves , FreeZone

d.wint

jleitao
Posting Yak Master

100 Posts

Posted - 2015-02-09 : 19:20:02

If doesn't work can you post a sample of tblDriverName?

------------------------
PS - Sorry my bad english

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources