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

[Ron]

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