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

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

Postby cypherinfo » Wed Jan 24, 2018 6:59 am

Congratulations since an old issue: [video] Browse object doesn't load when using Firefox! https://forums.nubuilder.com/viewtopic.php?f=13&t=9131&p=15043&hilit=browse+object#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.
cypherinfo
 
Posts: 124
Joined: Fri Feb 19, 2010 2:12 am

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

Postby toms » Wed Jan 24, 2018 10:57 am

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...
toms
 

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

Postby cypherinfo » Thu Jan 25, 2018 2:47 am

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: 124
Joined: Fri Feb 19, 2010 2:12 am

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

Postby cypherinfo » Sat Jan 27, 2018 12:23 am

Wow, what a weird thing!
I have just found the eason of the issue :)
Here is the thread that triggered it: https://forums.nubuilder.com/viewtopic.php?f=13&t=8724&p=13649&hilit=+many+Records+on+look+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!
cypherinfo
 
Posts: 124
Joined: Fri Feb 19, 2010 2:12 am


Return to General



cron