Relation between tables with linked fields
lazarus / 16-Databases

----------------------------------------------------------------------
ERICGUIL · 2020-04-10 07:06

Hello,

I have looked different documentations but I didn't find information
about creating relation between tables. My purpose is to link
diffferent tables with common field . The example in my mind is
GetParentRow and GetChilRow in MS VB.
example:
Table customer : ID, Name, Adress ...
Table Product: ID, Designation, Price ...
Table Invoice : ID Invoice, ID Customer, ID Product, Qty ...
In my program, for example, I would like to use fields as
Invoice ID, Customer name and adress (linked with common ID customer-
Invoice), Product Name and Price (linked with common ID Product -
Invoice).
Hope this is clear enough.
Thanks in advance.

----------------------------------------------------------------------
egsuh · 2020-04-11 04:59

This is operating on tables directly, not via SQL.


If your DB is SQLite3, then you may use TSQLite3DataSet, setting
MasterSource, MasterFields, and IndexFieldNames.

Otherwise, you would have to use TVirtualTable from pgDAC or
something. But only 30-day test version is available for Lazarus.

Or you can set Filters.

----------------------------------------------------------------------
SamC · 2020-04-11 05:50

Hello,

you can try then following:


1. (Invoice) SQL: 'select id, customerID, productID from invoice' =
qryInvoice
    DataSourceInvoice.DataSet = qryInvoice

2. (Customer) SQL: 'select id, name, adress from customer where
id=:customerID' = qryCustomer
    qryCustomer.DataSource = DataSourceInvoice

3. (Product) SQL: 'select id name, price from product where
id=:productID' = qryProduct
    qryProduct.DataSource = DataSourceInvoice

----------------------------------------------------------------------
ERICGUIL · 2020-04-11 07:50

Hello,

It seems to be easy to do. I will try it and return to you soon. Many
thanks.