Ron_1st schreef: > On Thursday 29 January 2009, Ron wrote: > >> Bit stuck. >> >> In an attempt to optimize some db queries. >> >> I changed this (gets all events first then check if enabled, then gets >> the trigger fields for that event etc): >> ---- >> DIM rResult, rResultTrig, rResultAction AS Result >> >> rResult = Main.hDB.Exec("SELECT * FROM events") >> IF rResult THEN >> IF rResult.Count THEN >> FOR EACH rResult >> IF rResult!enabled THEN >> IF rResult!trigger1 THEN >> rResultTrig = Main.hDB.Exec("SELECT * FROM triggers WHERE id >> = &1 ", rResult!trigger1) >> IF rResultTrig THEN >> IF rResultTrig!param1 = >> ... >> NEXT >> ---- >> >> to this (gets selected events and triggers table/fields): >> ---- >> DIM rResult AS Result >> >> rResult = Main.hDB.Exec("SELECT * FROM events, triggers WHERE >> events.trigger1 = triggers.id AND triggers.type = 3 AND events.enabled >> AND triggers.param1 = &1", iId) >> >> IF rResult THEN >> IF rResult.Count THEN >> >> FOR EACH rResult >> ... >> NEXT >> ---- >> In the sql query tool from database manager it returns exactly the >> correct records/fields etc >> >> But how can I get the fields from the triggers table from this resultset? >> >> I cannot do something like rResult!triggers.id >> Is this possible? >> >> Thanks. >> >> Regards, >> Ron_2nd. >> >> > > >> rResult = Main.hDB.Exec("SELECT * FROM events, triggers WHERE >> > rResult = Main.hDB.Exec("SELECT *, triggers.fieldx as "triggerfieldx" FROM > events, triggers WHERE > > You should get all field names from both tables side by side. > (As far I remember) > > You can use the as "triggerfield" to declare the specific field from triggers. > > Lets asume both have a ID field, normal result will be then > ID event xxx yyy ID trigger xxx yyy > > Using the ___triggers.ID as "triggerID"___ then you get > ID event xxx yyy ID trigger xxx yyy triggerID > > > > > > > Best regards, > > Ron_1st > >
Now that's the last kind of solution I was thinking of. Where this you get this info from, have been searching gambasdoc over and over again. But it works nicely! Note that single quotes are needed. --- DIM value AS String rResult = Main.hDB.Exec("SELECT *,triggers.param2 as 'value' FROM events, triggers WHERE events.trigger1 = triggers.id AND triggers.type = 3 AND events.enabled AND triggers.param1 = &1", iId) IF rResult THEN IF rResult.Count THEN FOR EACH rResult SELECT rResult!value ... --- Thanks alot!! Regard, Ron_2nd ------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user