Home Many to many relation hookup in Delphi
Reply: 1

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.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.36436 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO