[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