SQL Select query having a table input for a condition

I believe this is what you want...

SELECT T.iVehicleID, SUM(T.fTripDistance) AS KM
FROM [Trips] T
INNER JOIN [Conditions] C
ON T.iVehicleID = C.iVehicleID
GROUP BY T.iVehicleID

Select allOpen in new window

thanks for the response.  I think you meant to Group by T.iVehicleID instead of SUM(T.fTripDistance).

Having done so the query works but how is the dtDatetime condition met?

tnx

Lol...yes, I saw that and fixed it right before you posted...  :)

Added the date/time condition...

SELECT T.iVehicleID, SUM(T.fTripDistance) AS KM
FROM [Trips] T
INNER JOIN [Conditions] C
ON T.iVehicleID = C.iVehicleID
WHERE [C.dtDateTime]>='2011-11-17 19:01:46.300'
GROUP BY T.iVehicleID

Select allOpen in new window

Yes you fixed the Group by but I still have the issue of satisfying the second condition:

and [dtDateTime]>= {the  [dtDateTime] in the Conditions table for that iVehicleid}

haha, no prob. but still not what I need.  The dtDatetime is not constant it needs to be the dtDatetime in the Conditions table:

iVehicleID      dtDateTime
2                      2012-03-15 17:52:17.020
3                      2012-03-16 00:08:52.077
4                      2012-03-15 20:03:59.020
5                      2011-11-17 19:01:46.300
6                      2012-03-15 19:09:44.020

For this example if I was to do things the long way:

Select * FROM
(

Select [iVehicleID],SUM(fTripDistance) AS KM
  FROM Trips
  
WHERE [iVehicleID]=2

and [dtDateTime]>='2012-03-15 17:52:17.020'

  GROUP BY [iVehicleID]
) a

UNION ALL

SELECT * FROM

(

Select [iVehicleID],SUM(fTripDistance) AS KM
  FROM Trips
  
WHERE [iVehicleID]=3

and [dtDateTime]>='2012-03-16 00:08:52.077'

  GROUP BY [iVehicleID]

) as b

.
.
.

Select allOpen in new window

Sure let me clarify.  Yes the Trips table does have a dtDatetime field.  Here is what the trips table will look like

iVehicleID,   dtDateTime,                      fTripDistance
3               2011-11-16 19:01:46.300    100
5               2011-11-17 19:01:46.300    200
3               2011-11-18 19:01:46.300    100
3               2011-11-19 19:01:46.300    200
5               2011-11-20 19:01:46.300    100
....

From my original post:

Select [iVehicleID],SUM(fTripDistance) AS KM
  FROM Trips
  WHERE [iVehicleID]=5 
and [dtDateTime]>='2011-11-17 19:01:46.300'
  GROUP BY [iVehicleID]

Select allOpen in new window

The above gives me the total KMs traveled by the vehicleid 5 since '2011-11-17 19:01:46.300'.  For results I get say [5, 9972] (see first post).


Now I have the Conditions table which is unique by vehicleid.  

iVehicleID      dtDateTime
2                      2012-03-15 17:52:17.020
3                      2012-03-16 00:08:52.077
4                      2012-03-15 20:03:59.020
5                      2011-11-17 19:01:46.300
6                      2012-03-15 19:09:44.020


What I want is the total KMs traveled by each vehcile in the Conditions table but the KMs added should only be since the Trips.dtDateTime>=Conditons.dtDateTime.

but... I just tried something working off your suggestion and it seemed to work :)

  SELECT T.iVehicleID, SUM(T.fTripDistance) AS KM

FROM [Trips] T

INNER JOIN [Conditions] C

ON T.iVehicleID = C.iVehicleID

AND T.dtDateTime>=C.dtDateTime

GROUP BY T.iVehicleID

Select allOpen in new window

I think the using WHERE instead of ANDing the innerjoin also works:

 SELECT T.iVehicleID, SUM(T.fTripDistance) AS KM

FROM [Trips] T

INNER JOIN [Conditions] C

ON T.iVehicleID = C.iVehicleID

WHERE T.dtDateTime>=C.dtDateTime

GROUP BY T.iVehicleID

Select allOpen in new window

ha - I thought I had changed my previous comment fast enough before you actually saw it. O well... I think i understand now what you are asking. My previous post should be what you need. I hope...

Perfect. I like your's better as it returns all rows in Condition table where mine only returns the rows where the condition is satisfied. Thanks for your help.