Solved

Inner Join on multiple fields?

Posted on 2014-11-27
5
249 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

809 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