Solved

Inner Join on multiple fields?

Posted on 2014-11-27
5
244 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
  • 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 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 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