[prev in list] [next in list] [prev in thread] [next in thread] 

List:       gambas-user
Subject:    Re: [Gambas-user] How to address more than one table in resultset
From:       Ron <ron () cyberjunky ! nl>
Date:       2009-01-29 13:42:02
Message-ID: 4981B22A.2040108 () cyberjunky ! nl
[Download RAW message or body]

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
[prev in list] [next in list] [prev in thread] [next in thread]