Double Inner Join - Incorrect Results

Question

I have this SQL Query, I need to Join my VehicleMonitoring Log Table twice as I need values from Enter and Exit Events where 6 is enter and 7 is Exit.

The Problem is that Enter Time is always the same when I run this script.

Select top 100 aEnter.iAssetId as AssetEnter, aEnter.iAssetId as AssetExit, vmEnter.dtUTCDateTime, g.iGeofenceId, g.sGeofenceName, c.sCategoryName, c.iCategoryId, s.sSiteName, s.iSiteId, vmEnter.dtUTCDateTime as EnterTime, vmExit.dtUTCDateTime as ExitTime --, --convert(char(8),dateadd(s,DATEDIFF(s,vmEnter.dtUTCDateTime, vmExit.dtUTCDateTime ),'1900-1-1'),8) as Times From Sites s Inner Join Categories c on s.iSiteId = c.iSiteId Inner Join Assets aEnter on c.iCategoryId = aEnter.iCategoryId Inner Join VehicleMonitoringLog vmEnter on vmEnter.iAssetId = aEnter.iAssetId and vmEnter.eEventCode = 6 Inner Join VehicleMonitoringLog vmExit on vmExit.iAssetId = aEnter.iAssetId and vmExit.eEventCode = 7 Inner Join Geofences g on g.iGeofenceId = vmEnter.iGeofenceId

Which join is incorrect?

enter image description here

The join is on iAssetID. It looks like your results show AssetEnter (iAssetId) as the same for all records, so I'd expect the Enter Time to follow suit. — Dave.Gugg
– Dave.Gugg, Commented Dec 10, 2014 at 17:50
looks like your code will list all of the entry and exit times, how do you know that the exit is related the a given entry? — mrtig
– mrtig, Commented Dec 10, 2014 at 17:52
I agree with mrtig. You need to define the relationship between the two aliases for vehicleMonitoringLog. — Jared_S
– Jared_S, Commented Dec 10, 2014 at 17:58
An asset can have more than one VehicleMonitoringLog with eEvent 6 or eEvent 7. — Dawood Awan
– Dawood Awan, Commented Dec 10, 2014 at 18:01

bowlturner · Accepted Answer · 2014-12-10 17:53:01Z

I believe you have a cross product between Assets and the two Vehicle tables.

if you didn't stop at top 100 you'd see after a while Entertime would change and be the same again for a while.

You need to have two asset tables as well as 2 VehiclMontioringLog tables. One for the AssetEnter and one for AssetExit.

Select top 100 aEnter.iAssetId as AssetEnter, aExit.iAssetId as AssetExit, vmEnter.dtUTCDateTime, g.iGeofenceId From Sites s Inner Join Categories c on s.iSiteId = c.iSiteId Inner Join Assets eEnter on c.iCategoryId = aEnter.iCategoryId Inner Join Assets aExit on c.iCategoryId = aExit.iCategoryId Inner Join VehicleMonitoringLog vmEnter on vmEnter.iAssetId = aEnter.iAssetId and vmEnter.eEventCode = 6 Inner Join VehicleMonitoringLog vmExit on vmExit.iAssetId = aExit.iAssetId and vmExit.eEventCode = 7 Inner Join Geofences g on g.iGeofenceId = vmEnter.iGeofenceId

How do I double join the Assets table and VehicleMonitoring talbe?
@DawoodAwan more like this, you need to change the select clause too

Collectives™ on Stack Overflow

Double Inner Join - Incorrect Results

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related