[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