CODEWITHSUNDEEP
sqlsql-serverjoin
Dawood Awan
Dawood Awan

Reputation: 7318

Double Inner Join - Incorrect Results

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

Upvotes: 1

Views: 65

Answers (1)

bowlturner
bowlturner

Reputation: 2016

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

Upvotes: 3

Related Questions