Join on form

Join on form

Postby chrisb » Mon Oct 29, 2012 3:08 pm

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
chrisb
 
Posts: 12
Joined: Mon Oct 29, 2012 1:48 pm

Re: Join on form

Postby admin » Tue Oct 30, 2012 8:54 am

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
admin
Site Admin
 
Posts: 3162
Joined: Mon Jun 15, 2009 9:53 am

Re: Join on form

Postby chrisb » Tue Oct 30, 2012 9:01 pm

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.
chrisb
 
Posts: 12
Joined: Mon Oct 29, 2012 1:48 pm

Re: Join on form

Postby admin » Wed Oct 31, 2012 2:40 am

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
admin
Site Admin
 
Posts: 3162
Joined: Mon Jun 15, 2009 9:53 am

Re: Join on form

Postby 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
alejandrodgb
 
Posts: 1
Joined: Tue Jan 13, 2015 1:48 pm

Re: Join on form

Postby massiws » Sun Jan 18, 2015 7:19 am

alejandrodgb, your have write correctly the SQL statement in Campaign form:
General_tab.png
General tab on Campaign form
General_tab.png (17.8 KiB) Viewed 9428 times

Browse_tab.png
Browse tab on Campaign form
Browse_tab.png (24.62 KiB) Viewed 9428 times


Hope this helps,
Max
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy


Return to General