Database

24-May-04 21:27

Hi there!

here is my problem:
using a couple of queries, i put 2 datatables in a dataset, now i need to do one more query on those 2 datatables. how can i do this last query without writing the datatables on disk first?

thanks in advance,
fuel2run

ekolis25-May-04 9:02

25-May-04 9:02

The DataTable class does have a Select method, which runs simple SELECT queries against the DataTable and returns an array of DataRows. I believe this only works with single-table queries, though, so I'm not sure how useful this will be... there's also the DataView component, but IIRC that has the same limitations; it probably just calls the Select method of its DataSource or something.

fuel2run25-May-04 21:08

25-May-04 21:08

do you thinkthere's any way to do the query i need?
basically i don't want to write the 2 temporary tables on disk

any advice?

thanks!
fuel2run

ekolis26-May-04 10:58

26-May-04 10:58

What query are you doing on what tables, and what temporary tables do you think you need to create? I'm a bit unclear on exactly what you're trying to accomplish other than "run a query on tables in memory which involves creating temporary tables"...

fuel2run27-May-04 4:34

27-May-04 4:34

i need the 2 temporary tables because i'm quering 2 times the same tables and then i'm doing a third query to cross the results

basically, i'm looking for the right bus (it's a bus timetable) to go from some origin to a destination (so i create the temp Table "from", the temp table "TO" and cross them)
it works, but it's horrible and slow

thanks
fuel2run

ekolis27-May-04 10:53

27-May-04 10:53

So if I've got this straight, you've got a single Buses table with a From and a To field, you're creating a From table with all the buses from some specific location and a To table with all the buses to a specific location, and you want to find the record which is in both those tables?

If that's all you want to do, why not put both conditions in one query and save yourself the trouble of creating the temp tables in the first place?

SELECT * FROM Buses WHERE [From] = ? AND [To] = ?

Or am I missing something here?

fuel2run27-May-04 21:04

27-May-04 21:04

hi ekolis,

well, yes you are missing something, but that's why i haven' been clear enough,

the FROM and TO aren't necessarily the start end stop of the bus route, but they can be intermediate stops.
moreover i'v got a table with the sequence of stops for each bus route (every bus route has the same code and this is what i'm looking for). i'haven't got a column called "STARTS AT" nor a column "ENDS AT", i only have a column "STOPS AT".

so a condition like "WHERE [From] = ? AND [To] = ?" can't exists
thas's why i'm confused...

any advice?

thanks anyway for your suggestions and for interesting.
cheers!
fuel2run

ekolis28-May-04 9:28

28-May-04 9:28

So I suppose you have 3 fields: BusID, StopTime, and StopLocation, let's call them? And the primary key is BusID and StopTime? Why don't you try creating a query, call it BusSchedule, which looks like this:

SELECT * FROM BusStops WHERE BusID = ? ORDER BY StopTime

Now run this query in a foreach loop on BusID, and it will give you the bus's schedule in the standard timetable format. (Assuming StopTime is stored as a DateTime and not as a string!) Now just grab the first and last record from this query

drFirst = dtQuery.Rows[0];
drLast = dtQuery.Rows[dtQuery.Rows.Count - 1];

and compare drFirst["StopLocation"] to your desired start location and drLast["StopLocation"] to your desired end location. If both equalities hold true, then the BusID you just ran the query on is the bus you're looking for and you can break out of the foreach loop.

Is this the answer you're looking for, or do I still not have your table structure right?

fuel2run29-May-04 2:55

29-May-04 2:55

well,
you figured out the table structure correctly, the primary key is BusID only, StopTime is a DateTime Field, but i can't use a query like this because "Origin" and "Destination" of the travel aren't necessarily the first and last stops of the bus travel.

my query would answer a question like: "i want go from, say, philadelphia to chicago leaving at 10.00am (within a range of a couple of hours: 10.00-12.00am), which bus i have to take?"

so i might take a bus stopping at philadelphia and at chicago but started somewhere else and destinated to somwhere else again.

i'm gettin' lost!

cheers!
fuel2run

ekolis29-May-04 9:15

29-May-04 9:15

If your primary key is only BusID, how can a single bus have multiple stops? It would need to be listed multiple times (once for each stop), but that wouldn't be allowed by the primary key...

I think I see what you're getting at with the "cross query" you wanted to use - I've never done any of those before, but it seems from what you've said that a cross query returns all records that are present in both tables you're comparing. So what you were originally doing is you had a query which returned all the buses that stopped at the source location and a query which returned all the buses that stopped at the target location? I guess what you could do then is something like this:

TimeSpan tsBest = tsBest.MaxValue;
string strBest = null;
foreach (DataRow drFrom in tblFrom.Rows)
{
// See if the bus is also in the "to" table
DataRow drTo = tblTo.Find(drFrom["BusID"]);
if (drTo != null)
{
// See if the bus stops at the from location *before* stopping
// at the to location (note: will not work if buses run past midnight!)
DateTime dtFromTime = (DateTime)drFrom["StopTime"];
DateTime dtToTime = (DateTime)drTo["StopTime"];
if (dtFromTime < dtToTime)
{
// See if bus leaves with desired range
if (dtFromTime <= dtDesiredMaxFromTime && dtFromTime >= dtDesiredMinFromTime)
{
// found a bus
// note: will not necessarily find the bus CLOSEST to the desired
// departure time, only the FIRST bus listed that is acceptable
strBest = drFrom["BusID"].ToString();
break;

}
}
}
else
{
// no bus goes here to there
strBest = null;
}
}

fuel2run29-May-04 22:14

29-May-04 22:14

hi ekolis,

you're quite right once again, i didn't explain enough, some more informations for you:

i'm trying to convert to asp.net something i've done inside access writing some vb code in a module and, most iportant, i have 3 tables to query from:

- "TabLines"= LineID, LineName
- "TabRoutes"=RouteID, LineID (related to LineID in "TabLines"), .. more data ...
- "TabStops"=LineID, RouteID, StopTime, ...more data...

"TabLines"' key field is LineID, RouteID and LineID are both key fields in "TabRoutes" and they are related to the same fields in "TabStops" where there are no key fields.

the procedures in access can find every bus stopping in the "from" place within a range of 2 hours from the time specified by input and stopping in the "to" place

i create the "ToTable" and write it on disk, create the "FromTable" and write it as well and then query those tables:

..."WHERE ToTable.LineID=FromTable.LineID AND ToTable.RouteID=FromTable.RouteID AND ToTable.StopTime>FromTable.StopTime"

i never thought to past midnight! this is one more problem!

Cheers!
fuel2run

ekolis30-May-04 15:50

30-May-04 15:50

So the procedures generate the FromTable and the ToTable, and you then query those tables? If that's the case, can I see the procedure code? I've never done anything with Access procedures before, but I imagine they're similar enough to VB.NET that I could at least help translate them... if I'm mistaken again, would it be too much trouble to email me your Access file (with any sensitive data removed) so I can understand what's really going on?

fuel2run30-May-04 23:55

30-May-04 23:55

well, i might send you the access file, but bear in mind this is in italian and almost not commented,
moreover, my problem is finding a different way from the one i used in the access file while simply translating froom vb to vb.net should be easy.

anyway, if you still want to take a look at the file let me know, i might send you the db with empty tables.

cheers!
fuel2run

ekolis31-May-04 13:54

31-May-04 13:54

Well, sorry that I've been unable to help you thus far. Perhaps we can look at some of your assumptions and try to work around them:

1. Why is it necessary that you not write the temporary tables to disk?
2. Why is it necessary that you do this from scratch (i.e. not use the VBA code or convert it in a simple way?)
3... ???

fuel2run31-May-04 22:02