Fred04
Member
Hello,
I have a databasejoin element (called `idloc` from table `location`) with the following WHERE condition:
where location.idcomp IN (SELECT user.idcomp FROM user WHERE user.idjuser = {$my->id})
it works great untill I also select the option Filter as 'drop down' (because I want my user to have the option to filter the list).
I have the following SQL warning and the filter drop down list only displays 'all'
filter query error: idloc You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND user.idjuser = 43) ORDER BY `location`.`descr` ASC LIMIT 0, 100' at line 13 SQL=SELECT DISTINCT(`location`.`descr`) AS `text`, `question`.`idloc` AS `value` FROM `question`LEFT JOIN `category` AS `category` ON `category`.`id` = `question`.`idcat` LEFT JOIN `calendardlist` AS `calendardlist` ON `calendardlist`.`id` = `question`.`idcal` LEFT JOIN `criticality` AS `criticality` ON `criticality`.`id` = `question`.`idcrit` LEFT JOIN `system` AS `system` ON `system`.`id` = `question`.`idsyst` LEFT JOIN `comparator` AS `comparator` ON `comparator`.`id` = `question`.`idcompa` LEFT JOIN `company` AS `company` ON `company`.`id` = `question`.`idcomp` LEFT JOIN `location` AS `location` ON `location`.`id` = `question`.`idloc` LEFT JOIN `domain` AS `domain` ON `domain`.`id` = `question`.`iddom` LEFT JOIN `subdomain` AS `subdomain` ON `subdomain`.`id` = `question`.`idsubdom` LEFT JOIN `question_type` AS `question_type` ON `question_type`.`id` = `question`.`idques_type` LEFT JOIN `status` AS `status` ON `status`.`id` = `question`.`pareto` WHERE `question`.`idloc` IN ('2','1')AND location.idcomp IN (SELECT user.idcomp FROM user AND user.idjuser = 43) ORDER BY `location`.`descr` ASC LIMIT 0, 100
for whatever reason the WHERE as been replaced by AND
Any explanation?
see attached pictures:
1) 'where' statement in my databasejoin element
2) option 'filter' for the same element
3) error when I display the list
I have a databasejoin element (called `idloc` from table `location`) with the following WHERE condition:
where location.idcomp IN (SELECT user.idcomp FROM user WHERE user.idjuser = {$my->id})
it works great untill I also select the option Filter as 'drop down' (because I want my user to have the option to filter the list).
I have the following SQL warning and the filter drop down list only displays 'all'
filter query error: idloc You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND user.idjuser = 43) ORDER BY `location`.`descr` ASC LIMIT 0, 100' at line 13 SQL=SELECT DISTINCT(`location`.`descr`) AS `text`, `question`.`idloc` AS `value` FROM `question`LEFT JOIN `category` AS `category` ON `category`.`id` = `question`.`idcat` LEFT JOIN `calendardlist` AS `calendardlist` ON `calendardlist`.`id` = `question`.`idcal` LEFT JOIN `criticality` AS `criticality` ON `criticality`.`id` = `question`.`idcrit` LEFT JOIN `system` AS `system` ON `system`.`id` = `question`.`idsyst` LEFT JOIN `comparator` AS `comparator` ON `comparator`.`id` = `question`.`idcompa` LEFT JOIN `company` AS `company` ON `company`.`id` = `question`.`idcomp` LEFT JOIN `location` AS `location` ON `location`.`id` = `question`.`idloc` LEFT JOIN `domain` AS `domain` ON `domain`.`id` = `question`.`iddom` LEFT JOIN `subdomain` AS `subdomain` ON `subdomain`.`id` = `question`.`idsubdom` LEFT JOIN `question_type` AS `question_type` ON `question_type`.`id` = `question`.`idques_type` LEFT JOIN `status` AS `status` ON `status`.`id` = `question`.`pareto` WHERE `question`.`idloc` IN ('2','1')AND location.idcomp IN (SELECT user.idcomp FROM user AND user.idjuser = 43) ORDER BY `location`.`descr` ASC LIMIT 0, 100
for whatever reason the WHERE as been replaced by AND
Any explanation?
see attached pictures:
1) 'where' statement in my databasejoin element
2) option 'filter' for the same element
3) error when I display the list
