[ic] SQL Join issues in query tag
Rick Bragg
lists at gmnet.net
Thu Apr 23 15:11:27 UTC 2009
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!
[/affiliates_list]
[/query]
Problem 1:
First, I can never get the data of the privilege number in the list,
Problem 2:
Second, I get the following error with the ORDER BY ABS line:
Query on table affiliate failed: DBD::mysql::st execute failed: FUNCTION montpelier.ABS does not exist at /usr/local/interchange-5.6.1-dev/lib/Vend/Table/DBI.pm line 2004.
So I take out the ABS () and it seems to be fine. I can use the ABS
function in other non-join queries fine though.
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