I have created a database that include some many-to-many tables.
I am trying to retrieve the name from a table, instead of its id. It is easy with one-to-many, but I couldn't yet with many-to-many.
All the tables are linked in Tables relationship.
In Query I created INNER JOIN and included the fields in "column"
SELECT
procedrealizado.id,
procedrealizado.id_proced,
procedrealizado.id_hospital,
procedrealizado.id_convenio,
procedrealizado.`data`,
procedrealizado.reembolso,
procedrealizado.id_paciente,
procedrealizado.obs,
procedrealizado.id_cirurgiao,
procedrealizado.id_1aux,
procedrealizado.id_2aux,
procedrealizado.id_3aux,
procedrealizado.id_instrumentador,
procedrealizado.particular,
procedrealizado.laudo,
procedrealizado.laudo_thumbnail,
procedrealizado.descricao,
convenio.convenio,
hospitais.hospital,
hospitais.id AS id1,
convenio.id AS id2,
paciente.paciente,
paciente.id AS id3
FROM procedrealizado
INNER JOIN paciente ON procedrealizado.id_paciente = paciente.id
INNER JOIN hospitais ON procedrealizado.id_hospital = hospitais.id
INNER JOIN convenio ON procedrealizado.id_convenio = convenio.id
But when in "Editor" for the table "pagamento" I try to create a field that "Lookup Table" the "procedrealizado" but it does not include, in the "Display Field" the third table fields "paciente", "hospital" and "convenio" that where INNER JOINED in Query Design.
Any suggestion ????
I really think the big problem with PHPRunner is the difficulty to create many-to-many databases. That really limits my usage...
Thanks a lot