A Browse form containing a JOIN

Questions related to using nuBuilder Forte.

A Browse form containing a JOIN

Unread postby absalom » Mon Apr 05, 2021 12:08 am

Hi, as soon as I add a JOIN clause in my SQL request (browse FORM), it stops working (no record is returned); if I remove the clause (and the relevant fields to be displayed) I get the expected rows.

How can I handle this clause?
absalom
 
Posts: 22
Joined: Sat Apr 03, 2021 11:20 pm
nuBuilder Version: 4.5

Re: A Browse form containing a JOIN

Unread postby kev1n » Mon Apr 05, 2021 12:17 am

Can you post your SQL?
kev1n
nuBuilder Team
 
Posts: 1622
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: A Browse form containing a JOIN

Unread postby absalom » Mon Apr 05, 2021 12:44 am

My SQL is:

Code: Select all
SELECT consultations.date, patients.nom, patients.prenom, consultations.type, consultations.prix, consultations.type_reglement
  FROM consultations JOIN patients ON consultations.id_patient = patients.id
  WHERE consultations.date_encaissement IS NULL
ORDER BY consultations.date DESC


Result is as expected on phpMyAdmin
absalom
 
Posts: 22
Joined: Sat Apr 03, 2021 11:20 pm
nuBuilder Version: 4.5

Re: A Browse form containing a JOIN

Unread postby kev1n » Mon Apr 05, 2021 1:01 am

Make sure that the column names in the column Display are written exactly as in the SELECT statement.

sql.png
sql.png (33.98 KiB) Viewed 31 times


If you press CTRL+SHIFT+I (Options Menu -> Form Info) you will see the generated SQL.
kev1n
nuBuilder Team
 
Posts: 1622
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: A Browse form containing a JOIN

Unread postby absalom » Mon Apr 05, 2021 1:10 am

Both tables have 'id' as their primary key fields, and the generated SQL tries to select the ambiguous field 'id'.

Do I have to change the name of my column in the database and fix the change everywhere in NuBuilder, or can I still do it as it?
absalom
 
Posts: 22
Joined: Sat Apr 03, 2021 11:20 pm
nuBuilder Version: 4.5

Re: A Browse form containing a JOIN

Unread postby absalom » Mon Apr 05, 2021 1:25 am

Finally did it by creating a view with the JOIN instead
absalom
 
Posts: 22
Joined: Sat Apr 03, 2021 11:20 pm
nuBuilder Version: 4.5

Re: A Browse form containing a JOIN

Unread postby kev1n » Mon Apr 05, 2021 1:28 am

Could could try a SELECT ... FROM ... SELECT:

Code: Select all
SELECT date, nom, prenom, type, prix, type_reglement FROM (
SELECT consultations.id, consultations.date, patients.nom, patients.prenom, consultations.type, consultations.prix, consultations.type_reglement
  FROM consultations JOIN patients ON consultations.id_patient = patients.id
  WHERE consultations.date_encaissement IS NULL
) T
ORDER BY consultations.date DESC
kev1n
nuBuilder Team
 
Posts: 1622
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5


Return to General

Who is online

Users browsing this forum: GlenMcCabe and 21 guests