Join on form

[chrisb]

I'm migrating to nuBuilder because I want an open source cloud replacement for open office. Right now, I'm having problems with joins on forms I've created using the form wizard. The following SQL query works fine in phpMyAdmin. (I've tried a left join also)

SELECT *
FROM judge
JOIN school
ON judge.id_school = school.id

When I use it in the SQL box on the form page, I get an SQL error page that goes on forever. Is there a reason the query works in phpMyAdmin, but in the nuBuilder it doesn't.

I'm trying to set this up so I can use columns other than the primary ID for browse columns and for other reporting/display needs

[admin]

chrisb,

I'm not sure of the answer but you will probably find it easy to break nuBuilder if you don't base all of your Forms on using primary keys.

Maybe posting a screen shot might help.

Steven

[chrisb]

I've got the form part figured out now. The look UPS work great. My end users are going to be high school students and things need to be really simple and direct. On the browse screens, I'd like to display columns from other tables. My FKs are numbers because that's what I've always usedand been taught. But I'd like to generate browse screens that can display information based on a join. The sql query above joins twotables i'd like to display as one browse screen so information from the second joined table shows up beside my FK numbers.

[admin]

chrisb,

You can display any fields from joined tables on the Browse Form.

I don't think I understand your question.

Sometimes if you include screen captures it may help.

Steven

[alejandrodgb]

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

[massiws]

alejandrodgb, your have write correctly the SQL statement in Campaign form:

General tab on Campaign form

General_tab.png (17.8 KiB) Viewed 10301 times

Browse tab on Campaign form

Browse_tab.png (24.62 KiB) Viewed 10301 times

Hope this helps,
Max