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

[Ron_1st]

On Thursday 29 January 2009, Ron wrote:
> 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
> 

Experience with MS-Access-MySQL combination, used a lot in the past,
and programming with php for websites.

About the quotes, to be exactly there should be backticks for MySQL 
field/table names too.
In first case I had them but you did not used it for the table name
so I deleted them.

Normal to be safe MySQL use backticks for field and table names, special
when spaces and non-alpha characters are involved in them.
For the alias name after AS you could using single or double quotes.
Depends on to front and end quote used if passed as string.

The reason wy it is not in the gambas doc is simple.
The default method does not using DB.exec() while this is backend sensitive.
Your code runs now on MySQL but could fail on SQLite or Firebird!

Your construction now is more for advanced programmers and that is
not default for the average gambas user ATM.

Leuk die twee ronnies die in het engels pruttellen. :)


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?