Home Many to many relation hookup in Delphi

# Many to many relation hookup in Delphi

Edward
1#
Edward Published in 2017-11-11 20:19:53Z
 How do I do a many to many (master-master) relation in Delphi, I cannot find an example. Only dblookups and master-detail which I understand. Like a product can belong to one or more categories. Like this table structure: Product Table ProductId, ProductName CategoryTable CategoryId, CategoryName Relation table ProductId, CategoryId Ideally if you select a record in the product grid the edit of a product in a detail record is started in the right side of the screen. In here you can edit the product properties an ideally you can select via checkboxes one or more categories in a checkbox group/grid. How do you hook this up with TTable of TQuery components? Is there a way? Hope you can help! Sincerely Edward Some more explaination: The goal is like: master [product grid list] Detail [on selected product] property **A** property **B** property **C** property **D** property **Category collection** Category 1 - checked Category 2 - unchecked Category 3 - unchecked Category 4 - checked 
Uwe Raabe
2#
Uwe Raabe Reply to 2017-11-12 15:49:24Z
 This can be transformed to a simple Master-Detail relation by using a joined query over Relation Table and Category Table (similar like this example): SELECT * FROM Relation a JOIN Category b on a.CategoryId = b.CategoryId WHERE ProductId = :ProductId  In case you want a list of all categories where a separate field indicates if a relation to the product exists, you can use a query like this (example for MSSQL, the new field is named Checked): SELECT c.*, CASE WHEN r.CategoryId IS NULL THEN 0 ELSE 1 END AS Checked FROM CATEGORY LEFT JOIN Relation r ON (c.CategoryId = r.CategoryId) AND (r.ProductId = :ProductId) ORDER BY c.CategoryId  Note that you have to write the code to add or delete the relation record when you manipulate the check list by yourself.
 You need to login account before you can post.
Processed in 0.36436 second(s) , Gzip On .