Welcome to the nuBuilder forums!

Please register and login to view forums and other content only available to registered users.

Column Sort doesn't work when used: INNER JOIN in SQL

Post Reply
cypherinfo
Posts: 125
Joined: Thu Feb 18, 2010 5:42 pm
Contact:

Column Sort doesn't work when used: INNER JOIN in SQL

Unread post by cypherinfo »

Congratulations since an old issue: [video] Browse object doesn't load when using Firefox! https://forums.nubuilder.cloud/viewtopic. ... ect#p15043 seems to be fixed.
I know that nuBuilder Pro is no more supported anyway: I have just noticed that when in the SQL section of in the General tab of the setting of a form is used a select with inner join instead of a select *, the browse from looses its ability to sort their columns, that is the reason why I had to: first create a table resulting from the Select inner join then create a form on it.
Is there anybody has found the fix?
Thank you.
toms
Posts: 785
Joined: Sun Oct 14, 2018 11:25 am

Re: Column Sort doesn't work when used: INNER JOIN in SQL

Unread post by toms »

Hi,

I use a left join in a browse form and its sql has the following structure.

Code: Select all

SELECT *
FROM
  (SELECT t1.field1,
          t2.field2,....
   FROM t1
   LEFT JOIN t2 ON t1.id = t2.otherid
   WHERE t1.field3 = 'something'
) t
ORDER BY field1
IIRC, I had to use a select * from a select to get it work and sorting works normally.
Other option: Create a view.
I hope that helps...
cypherinfo
Posts: 125
Joined: Thu Feb 18, 2010 5:42 pm
Contact:

Re: Column Sort doesn't work when used: INNER JOIN in SQL

Unread post by cypherinfo »

Since:

Code: Select all

SELECT * FROM A INNER JOIN b ON a.field1 = b.field2 
is equivalent to:

Code: Select all

SELECT * FROM a,b WHERE a.field1 = b.field2
In other words it is possible not to use the INNER keyword.
cypherinfo
Posts: 125
Joined: Thu Feb 18, 2010 5:42 pm
Contact:

Re: Column Sort doesn't work when used: INNER JOIN in SQL

Unread post by cypherinfo »

Wow, what a weird thing!
I have just found the eason of the issue :)
Here is the thread that triggered it: https://forums.nubuilder.cloud/viewtopic. ... up+#p13649
I noticed that even after having changed all of the INNER JOIN with its quivalent SELECT (field list) WHERE table1, table2 WHERE table1.field1 = table2.field2;
the issue occurred. That disappeared just avoiding to add the final ";" semicolon of the SELECT!
Post Reply