A Browse form containing a JOIN

[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?

[kev1n]

Can you post your SQL?

[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

[kev1n]

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

sql.png (33.98 KiB) Viewed 33 times

If you press CTRL+SHIFT+I (Options Menu -> Form Info) you will see the generated SQL.

[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?

[absalom]

Finally did it by creating a view with the JOIN instead

[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