Page 1 of 1
A Browse form containing a JOIN
Posted: Sun Apr 04, 2021 4:38 pm
by absalom
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?
Re: A Browse form containing a JOIN
Posted: Sun Apr 04, 2021 4:47 pm
by kev1n
Can you post your SQL?
Re: A Browse form containing a JOIN
Posted: Sun Apr 04, 2021 5:14 pm
by absalom
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
Re: A Browse form containing a JOIN
Posted: Sun Apr 04, 2021 5:31 pm
by kev1n
Make sure that the column names in the column Display are written exactly as in the SELECT statement.
sql.png
If you press CTRL+SHIFT+I (Options Menu -> Form Info) you will see the generated SQL.
Re: A Browse form containing a JOIN
Posted: Sun Apr 04, 2021 5:40 pm
by absalom
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?
Re: A Browse form containing a JOIN
Posted: Sun Apr 04, 2021 5:55 pm
by absalom
Finally did it by creating a view with the JOIN instead
Re: A Browse form containing a JOIN
Posted: Sun Apr 04, 2021 5:58 pm
by kev1n
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