[Gambas-user] How to address more than one table in resultset

[Ron_1st]

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

-- 
 A: Delete the text you reply on.
 Q: What to do to get my post on top?
 A: Because it messes up the order in which people normally read text. 
 Q: Why is top-posting such a bad thing? 
 A: Top-posting. 
 Q: What is the most annoying thing in e-mail?