[ic] SQL Join issues in query tag

Rick Bragg lists at gmnet.net
Thu Apr 23 15:44:51 UTC 2009


On Thu, 2009-04-23 at 17:16 +0200, Stefan Hornburg (Racke) wrote:
> Rick Bragg wrote:
> > Hi,
> > 
> > I have the following 2 tables, where the privileges.username is a key to
> > affiliate.affiliate.
> > 
> > Table: affiliate:
> > affiliate, fname, lname, ...
> > 
> > Table: privileges:
> > code, username, group_code, privilege ...
> > 
> > I have the following join, and the query works fine when I test it
> > strait on the database,
> > 
> >   SELECT affiliate.*, privileges.privilege
> >   FROM affiliate JOIN privileges
> >   ON (privileges.group_code = "101"
> >       AND privileges.username = affiliate.affiliate)
> >   ORDER BY ABS (privileges.privilege) DESC, affiliate.lname
> > 
> > 
> > 
> > There are 2 issues when I try to use it in the query tag like this:
> > 
> > [query
> >     type=list
> >     label=affiliates
> >     prefix=affiliates
> >     list_prefix=affiliates_list
> >     sql=|
> >   SELECT affiliate.*, privileges.privilege
> >   FROM affiliate JOIN privileges
> >   ON (privileges.group_code = "101"
> >       AND privileges.username = affiliate.affiliate)
> >   ORDER BY ABS (privileges.privilege) DESC, affiliate.lname
> >   |
> > ]
> > [affiliates_list]
> >   [affiliates-data affiliate fname]
> >   [affiliates-data affiliate fname]
> >   [affiliates-data privilege privilege] <-- This always returns nothing!
> > 
> 
> You already have these values in the returning records, so please try
> [affiliates-param fname], [affiliates-param privilege]
> 
> Regards
>          Racke
> 
> 
> 
> -- 
> LinuXia Systems => http://www.linuxia.de/
> Expert Interchange Consulting and System Administration
> ICDEVGROUP => http://www.icdevgroup.org/
> Interchange Development Team
> 

Oh!  right, that works.  Is there a performance penalty for using
[prefix-data tablename fieldname] instead of the param?

thanks
rick



-- 
This message has been scanned for viruses and
dangerous content by Green Mountain Network, and is
believed to be clean.




More information about the interchange-users mailing list