by alejandrodgb » Tue Jan 13, 2015 2:17 pm
Admin, I have the same question as Chris B. Although I understand you can do it, when I insert the SQL statement below:
SELECT states_id, states_name FROM states JOIN campaigns ON states.states_id=campaigns.camp_state_id;
in the Display section of the Browse tab I get the following error:
===USER========
globeadmin
===PDO MESSAGE=====
SQLSTATE[42000]: Syntax error or access violation: 1064 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 'SELECT states_id, states_name FROM states JOIN campaigns ON states.states_id=cam' at line 1
===SQL=======
SELECT campaigns_id,IFNULL(camp_name,''),IFNULL(SELECT states_id, states_name FROM states JOIN campaigns ON states.states_id=campaigns.camp_state_id;,'') from campaigns
===BACK TRACE=====
/var/www/html/nuapi.php - line 2019 (nuRunQuery)
/var/www/html/nuapi.php - line 1695 (nuGetBrowseRecords)
/var/www/html/nuapi.php - line 188 (nuGetBrowseForm)
In my case I want to display Campaign Name, State in the browse screen of the campaigns table. This table has the following structure: campaigns_id, camp_name, camp_state_id where camp_state_id is the PK of the states table which format is: states_id, states_name. Currently I am displaying the State as numbers (FK) and I want it to query the name from the states table.
Currently:
Campaign Name | State
Brisbane | 1
Sydney | 2
What I want to do:
Campaign Name | State
Brisbane | QLD
Sydney | NSW