?
Solved

Inner Join on multiple fields?

Posted on 2014-11-27
5
Medium Priority
?
264 Views
Last Modified: 2014-11-28
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?
0
Comment
Question by:websss
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40470008
Yes,

you can definitely do this .

Please go ahead
0
 
LVL 9

Expert Comment

by:teebon
ID: 40470010
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
 

Author Comment

by:websss
ID: 40470022
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
 
LVL 9

Accepted Solution

by:
teebon earned 2000 total points
ID: 40470033
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
 

Author Closing Comment

by:websss
ID: 40470158
ah thats my typo fault!
didn't spot that!
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question