Solved

Inner Join on multiple fields?

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

732 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