|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Probably very trivial SQL Query question....I'm a bit of an SQL newbie and have the following problem: I have two tables, one contains waypoints in latitude and longitude from a trip. The other contains trips. so: tabTrip: TripID, TripName tabWaypoints: fk_TripID, Lat, Lon, tstamp The relationship is such that one trip can have many waypoints. Each waypoint has a timestamp (when it got added to the table) How do I retrieve for each trip, that has waypoints not older than a certain time, the most recently added waypoint's latitude and longitude as well as the trip name (which is in the trip table) in one query? Somehow those queries need to be worked into one, but how? SELECT DISTINCT(fk_TripID) FROM tabWaypoint WHERE tstamp > '2006-08-18 20:00:00' and SELECT TOP(1) Lat, Lon FROM tabWaypoints WHERE tstamp > '2006-08-18 20:00:00' ORDER BY tstamp DESC and somehow, join the tabTrip so that I can select the TripName into this result set... Any help greatly appreciated, Thanks! - Balt Balt wrote:
Show quote > Hi all, select t.TripName, tw.Lat, tw.Lon, tw.tstamp> > I'm a bit of an SQL newbie and have the following problem: > > I have two tables, one contains waypoints in latitude and longitude from a > trip. > The other contains trips. > > so: > > tabTrip: TripID, TripName > tabWaypoints: fk_TripID, Lat, Lon, tstamp > > The relationship is such that one trip can have many waypoints. > > Each waypoint has a timestamp (when it got added to the table) > > How do I retrieve for each trip, that has waypoints not older than a certain > time, the most recently added waypoint's latitude and longitude as well as > the trip name (which is in the trip table) in one query? > > Somehow those queries need to be worked into one, but how? > > SELECT DISTINCT(fk_TripID) FROM tabWaypoint WHERE tstamp > '2006-08-18 > 20:00:00' > > and > > SELECT TOP(1) Lat, Lon FROM tabWaypoints WHERE tstamp > '2006-08-18 > 20:00:00' ORDER BY tstamp DESC > > and > > somehow, join the tabTrip so that I can select the TripName into this result > set... > > Any help greatly appreciated, Thanks! > > - Balt from tabTrip t inner join tabWaypoints tw on t.TripID = tw.TripID left join tabWaypoints later on t.TripID = later.TripID and later.tstamp > tw.tstamp where tw.tstamp > '2006-08-08T20:00:00' will that do you? Damien Damien,
your version retrieves an infinite amount of result sets... Stopped it after 500'000 records (but there are only 10'000 waypoints...) Cheers -Balt Show quote "Damien" wrote: > Balt wrote: > > Hi all, > > > > I'm a bit of an SQL newbie and have the following problem: > > > > I have two tables, one contains waypoints in latitude and longitude from a > > trip. > > The other contains trips. > > > > so: > > > > tabTrip: TripID, TripName > > tabWaypoints: fk_TripID, Lat, Lon, tstamp > > > > The relationship is such that one trip can have many waypoints. > > > > Each waypoint has a timestamp (when it got added to the table) > > > > How do I retrieve for each trip, that has waypoints not older than a certain > > time, the most recently added waypoint's latitude and longitude as well as > > the trip name (which is in the trip table) in one query? > > > > Somehow those queries need to be worked into one, but how? > > > > SELECT DISTINCT(fk_TripID) FROM tabWaypoint WHERE tstamp > '2006-08-18 > > 20:00:00' > > > > and > > > > SELECT TOP(1) Lat, Lon FROM tabWaypoints WHERE tstamp > '2006-08-18 > > 20:00:00' ORDER BY tstamp DESC > > > > and > > > > somehow, join the tabTrip so that I can select the TripName into this result > > set... > > > > Any help greatly appreciated, Thanks! > > > > - Balt > > select t.TripName, tw.Lat, tw.Lon, tw.tstamp > from > tabTrip t inner join tabWaypoints tw on t.TripID = tw.TripID left join > tabWaypoints later on t.TripID = later.TripID and later.tstamp > > tw.tstamp > where > tw.tstamp > '2006-08-08T20:00:00' > > will that do you? > > Damien > > Balt wrote:
Show quote > "Damien" wrote: Doh. As I was writing it, I kept saying to myself "remember to check> > > Balt wrote: > > > Hi all, > > > > > > I'm a bit of an SQL newbie and have the following problem: > > > > > > I have two tables, one contains waypoints in latitude and longitude from a > > > trip. > > > The other contains trips. > > > > > > so: > > > > > > tabTrip: TripID, TripName > > > tabWaypoints: fk_TripID, Lat, Lon, tstamp > > > > > > The relationship is such that one trip can have many waypoints. > > > > > > Each waypoint has a timestamp (when it got added to the table) > > > > > > How do I retrieve for each trip, that has waypoints not older than a certain > > > time, the most recently added waypoint's latitude and longitude as well as > > > the trip name (which is in the trip table) in one query? > > > > > > Somehow those queries need to be worked into one, but how? > > > > > > SELECT DISTINCT(fk_TripID) FROM tabWaypoint WHERE tstamp > '2006-08-18 > > > 20:00:00' > > > > > > and > > > > > > SELECT TOP(1) Lat, Lon FROM tabWaypoints WHERE tstamp > '2006-08-18 > > > 20:00:00' ORDER BY tstamp DESC > > > > > > and > > > > > > somehow, join the tabTrip so that I can select the TripName into this result > > > set... > > > > > > Any help greatly appreciated, Thanks! > > > > > > - Balt > > > > select t.TripName, tw.Lat, tw.Lon, tw.tstamp > > from > > tabTrip t inner join tabWaypoints tw on t.TripID = tw.TripID left join > > tabWaypoints later on t.TripID = later.TripID and later.tstamp > > > tw.tstamp > > where > > tw.tstamp > '2006-08-08T20:00:00' > > > > will that do you? > > > > Damien > > > > > Damien, > > your version retrieves an infinite amount of result sets... Stopped it after > 500'000 records (but there are only 10'000 waypoints...) > > Cheers > > -Balt > for null in the where clause, remember to check for null in the where clause...", which inevitably means I forgot to include "and later.TripID is null" in the where clause. Damien This should give you something to work with.
SELECT T.*, W.Lat, W.Long, W.tstamp FROM tabTrip as T JOIN tabWaypoints as W ON T.TripID = W.fw_TripID WHERE W.tstamp = (select max(tstamp) from tabWaypoints as M where W.tstamp = M.tstamp) AND W.tstamp > '2006-08-18 20:00:00' Roy Harvey Beacon Falls, CT On Fri, 18 Aug 2006 04:13:01 -0700, Balt <B***@discussions.microsoft.com> wrote: Show quote >Hi all, > >I'm a bit of an SQL newbie and have the following problem: > >I have two tables, one contains waypoints in latitude and longitude from a >trip. >The other contains trips. > >so: > >tabTrip: TripID, TripName >tabWaypoints: fk_TripID, Lat, Lon, tstamp > >The relationship is such that one trip can have many waypoints. > >Each waypoint has a timestamp (when it got added to the table) > >How do I retrieve for each trip, that has waypoints not older than a certain >time, the most recently added waypoint's latitude and longitude as well as >the trip name (which is in the trip table) in one query? > >Somehow those queries need to be worked into one, but how? > >SELECT DISTINCT(fk_TripID) FROM tabWaypoint WHERE tstamp > '2006-08-18 >20:00:00' > >and > >SELECT TOP(1) Lat, Lon FROM tabWaypoints WHERE tstamp > '2006-08-18 >20:00:00' ORDER BY tstamp DESC > >and > >somehow, join the tabTrip so that I can select the TripName into this result >set... > >Any help greatly appreciated, Thanks! > >- Balt > Hi Roy,
thanks, this almost works. It does return non distinct trip id's. I only need the most recent ONE position for each trip. Your query returns all of them. Cheers - Balt Show quote "Roy Harvey" wrote: > This should give you something to work with. > > SELECT T.*, W.Lat, W.Long, W.tstamp > FROM tabTrip as T > JOIN tabWaypoints as W > ON T.TripID = W.fw_TripID > WHERE W.tstamp = > (select max(tstamp) from tabWaypoints as M > where W.tstamp = M.tstamp) > AND W.tstamp > '2006-08-18 20:00:00' > > Roy Harvey > Beacon Falls, CT > > On Fri, 18 Aug 2006 04:13:01 -0700, Balt > <B***@discussions.microsoft.com> wrote: > > >Hi all, > > > >I'm a bit of an SQL newbie and have the following problem: > > > >I have two tables, one contains waypoints in latitude and longitude from a > >trip. > >The other contains trips. > > > >so: > > > >tabTrip: TripID, TripName > >tabWaypoints: fk_TripID, Lat, Lon, tstamp > > > >The relationship is such that one trip can have many waypoints. > > > >Each waypoint has a timestamp (when it got added to the table) > > > >How do I retrieve for each trip, that has waypoints not older than a certain > >time, the most recently added waypoint's latitude and longitude as well as > >the trip name (which is in the trip table) in one query? > > > >Somehow those queries need to be worked into one, but how? > > > >SELECT DISTINCT(fk_TripID) FROM tabWaypoint WHERE tstamp > '2006-08-18 > >20:00:00' > > > >and > > > >SELECT TOP(1) Lat, Lon FROM tabWaypoints WHERE tstamp > '2006-08-18 > >20:00:00' ORDER BY tstamp DESC > > > >and > > > >somehow, join the tabTrip so that I can select the TripName into this result > >set... > > > >Any help greatly appreciated, Thanks! > > > >- Balt > > > On Fri, 18 Aug 2006 15:25:02 -0700, Balt
<B***@discussions.microsoft.com> wrote: >Hi Roy, That's because I screwed up the subquer, I think. The corellation was> >thanks, this almost works. It does return non distinct trip id's. I only >need the most recent ONE position for each trip. Your query returns all of >them. > >Cheers > >- Balt supposed to be on fw_TripID. Maybe I did better this time: SELECT T.*, W.Lat, W.Long, W.tstamp FROM tabTrip as T JOIN tabWaypoints as W ON T.TripID = W.fw_TripID WHERE W.tstamp = (select max(tstamp) from tabWaypoints as M where W.fw_TripID = M.fw_TripID) AND W.tstamp > '2006-08-18 20:00:00' Roy That was it, thanks!
Guess I'll have to learn using sub-queries! Cheers - Balt Show quote "Roy Harvey" wrote: > On Fri, 18 Aug 2006 15:25:02 -0700, Balt > <B***@discussions.microsoft.com> wrote: > > >Hi Roy, > > > >thanks, this almost works. It does return non distinct trip id's. I only > >need the most recent ONE position for each trip. Your query returns all of > >them. > > > >Cheers > > > >- Balt > > That's because I screwed up the subquer, I think. The corellation was > supposed to be on fw_TripID. > > Maybe I did better this time: > > SELECT T.*, W.Lat, W.Long, W.tstamp > FROM tabTrip as T > JOIN tabWaypoints as W > ON T.TripID = W.fw_TripID > WHERE W.tstamp = > (select max(tstamp) from tabWaypoints as M > where W.fw_TripID = M.fw_TripID) > AND W.tstamp > '2006-08-18 20:00:00' > > Roy >  |
|||||||||||||||||||||||
Other interesting topics