0

I am using master-detail connection to show a list of assigned columns of a detail-table. In addition i want to show a second grid with not assigned detail columns

Example Master-Table t_human: idHU, nameHU (like John, Oscar, ...)
Detail-Table t_property: idPO, namePO (like male, female, blond hair, beard, blue eyes ...)
Join-Table t_hu_po: idHU, idPO
-> John - male, blond hair
-> Oscar - male, blue eyes, beard

When i select a master table row i want to show the assigned porperties and also the not assigned ones. The master-detail for the assigned properties works fine. But for the not-assigned properties doesn't work.

Detail-SQL: select p.idPO, p.namePO from t_property p where not exists (select * from t_hu_po hp where hp.idHU = :idHU and hp.idPO = p.idPO)

Master-SQL: select idHU, nameHU from human

When i try to select the detailfield in the delphi component (Devart, SDAC, TMSQuery) it only shows me the fields for p.idPO and p.namePO. The :idHU parameter in the statement is configured as parameter for the query in the component. When i manually type the idHU in the DetailFields property of the Dataset (TMSQuery) i get an Invalid column name 'idHU'.. from delphi when activating the query.

Does someone knows a better solution than doing it with events for datachange and manually setting the detail-parameter and refreshing the detail-query. My thought was, that master-detail would do the same thing but only in background, so that i don't have do code it.

Thanks
Stephan

Stephan Kallnik
  • 1
  • 1
  • 1

1 Answers1

0

Instead of using the where not exists try where p.idPO not in (Select idPO from t_hu_po where idHU=:idHu)

By makinge the subquery simply return all the idPOs that exist for the selected idHu, your query now looks for the properties that are not in that list.

JacalarRick
  • 153
  • 1
  • 8
  • The changed sql-statement, doesn't change the behavior of delphi. I don't get the field shown in Config-Dialog for master<->detail Field-selection and manually setting the fieldname in the property-editor run's into the error message _Invalid column name 'idHU'_ – Stephan Kallnik Sep 05 '18 at 06:07
  • This isn't a master-detail relationship, it is the exact opposite. You are looking for the entries which are not in the relationship! – JacalarRick Sep 05 '18 at 14:35