Inner Join on multiple fields?

Hi

Is it possible to have an inner join on multiple fields?

I have the following sql
  select  ipkTripID,dStartTime,dEndTime,isnull(iMaxSpeed,0) 
 as iMaxSpeed,isnull(iDistance,0) as iDistance,isnull(iAverageSpeed,0) 
 as iAverageSpeed,tm.vpkDeviceID, A.vTextMessage as startLocation,nStartEventInst,nEndEventInst  
 
 FROM tblTrip_Master tm  
inner  join tblCommonTrackingData A on A.vSequenceID = tm.nStartEventInst   

Open in new window


However, the inner join is on a field which isn't unique, and is bringing back duplicates,
so i would like to say something like this
inner  join tblCommonTrackingData A on A.vSequenceID = tm.nStartEventInst   
AND on A.vpkDeviceID = tm.vpkDeviceID

Open in new window


is it possible to do something like this?
websssAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
Yes,

you can definitely do this .

Please go ahead
0
teebonProduct ManagerCommented:
Hi websss,

The 'on' no need to be specified again:

  select  ipkTripID,dStartTime,dEndTime,isnull(iMaxSpeed,0) 
 as iMaxSpeed,isnull(iDistance,0) as iDistance,isnull(iAverageSpeed,0) 
 as iAverageSpeed,tm.vpkDeviceID, A.vTextMessage as startLocation,nStartEventInst,nEndEventInst  
 
 FROM tblTrip_Master tm  
inner  join tblCommonTrackingData A on A.vSequenceID = tm.nStartEventInst   
AND A.vpkDeviceID = tm.vpkDeviceID

Open in new window

0
websssAuthor Commented:
Thanks Teebon, that worked

I need to adjust the logic a bit... currently i have this in main statement

inner  join tblCommonTrackingData A on A.vSequenceID = tm.nStartEventInst   AND  A.vpkDeviceID = tm.vpkDeviceID
inner  join tblCommonTrackingData B on B.vSequenceID = tm.nEndEventInst   AND  A.vpkDeviceID = tm.vpkDeviceID

Open in new window


However, this is causing duplicates

i tried just this
inner  join tblCommonTrackingData A on  A.vSequenceID = tm.nEndEventInst  AND  A.vpkDeviceID = tm.vpkDeviceID

Open in new window


which works fine, however, I can only look up one tblCommonTrackingData. vTextMessage
i need to use these ID's from TRIP table:
nStartEventInst
nEndEventInst

To look up tblCommonTrackingData. vTextMessage (i.e. the physical address) for both ID's



The full statement is
 select  ipkTripID
		,vDeviceName
		,dStartTime
		,dEndTime
		,isnull(iMaxSpeed,0) 
		as iMaxSpeed
		,isnull(iDistance,0) as iDistance
		,isnull(iAverageSpeed,0) 
		as iAverageSpeed
		,tm.vpkDeviceID
		,A.vTextMessage as startLocation
		,B.vTextMessage as endLocation
		,nStartEventInst
		,nEndEventInst  
 
 FROM tblTrip_Master tm  
inner  join tblCommonTrackingData A on A.vSequenceID = tm.nStartEventInst   AND  A.vpkDeviceID = tm.vpkDeviceID
inner  join tblCommonTrackingData B on B.vSequenceID = tm.nEndEventInst   AND  A.vpkDeviceID = tm.vpkDeviceID
 inner  join Newtbl_DeviceMaster on nIMEINo=tm.vpkDeviceID  
 where  dStartTime >='2014/11/27 03:00:00' and  dEndTime <='2014/11/28 03:00:00'  and iParent =2262 
 ORDER BY dStartTime DESC

Open in new window

however, this show duplicates

How can i adjust the above to lookup the vTextMessage for tm.nEndEventInst and for tm.nStartEventInst without showing duplicates
0
teebonProduct ManagerCommented:
Can you try

 select  ipkTripID
		,vDeviceName
		,dStartTime
		,dEndTime
		,isnull(iMaxSpeed,0) 
		as iMaxSpeed
		,isnull(iDistance,0) as iDistance
		,isnull(iAverageSpeed,0) 
		as iAverageSpeed
		,tm.vpkDeviceID
		,A.vTextMessage as startLocation
		,B.vTextMessage as endLocation
		,nStartEventInst
		,nEndEventInst  
 
 FROM tblTrip_Master tm  
inner  join tblCommonTrackingData A on A.vSequenceID = tm.nStartEventInst   AND  A.vpkDeviceID = tm.vpkDeviceID
inner  join tblCommonTrackingData B on B.vSequenceID = tm.nEndEventInst   AND  B.vpkDeviceID = A.vpkDeviceID
 inner  join Newtbl_DeviceMaster on nIMEINo=tm.vpkDeviceID  
 where  dStartTime >='2014/11/27 03:00:00' and  dEndTime <='2014/11/28 03:00:00'  and iParent =2262 
 ORDER BY dStartTime DESC
                                          

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
websssAuthor Commented:
ah thats my typo fault!
didn't spot that!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.