[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