[ic] SQL Join issues in query tag

Stefan Hornburg (Racke) racke at linuxia.de
Thu Apr 23 15:16:20 UTC 2009


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




More information about the interchange-users mailing list