Master/Detail Relationship

[tonydm]

Hi,

I'm a new user of ZeosDBO and have question. I am trying to define a master/detail connection between two zQuery components. I set the MasterSource and MasterFields to the controlling Query. And I have set the Filter to a field in both the master and detail query. Making sure that the Filtered=true. But my detail contains more records than it should. I'm not even sure if I need to use the Filter or if I am, what I should be setting it to. My example:

Query1 (SELECT * FROM Orders;)
... (relative field listed)
OrderID (integer, autoinc, primary)
...

Query2 (SELECT * FROM OrderDetails;)
... (relative field listed)
OrderDetailsID (integer, autoinc, primary)
OrderID (integer)
...
MasterSource=Query1
MasterFields=OrderID
Filter=OrderID=OrderID
Filtered=true

Any help would be appreciated. Thanks

Tony

[btrewern]

I always use the Datasource property to do Master-Detail queries. Like the following:

Code: Select all

Query1.SQL.Text := 'SELECT * FROM Orders;';
DataSource1.Dataset := Query1;
Query2.SQL.Text := 'SELECT * FROM OrderDetails WHERE OrderID = :OrderID';
Query2.DataSource := DataSource1;

or something like that.

Hope this helps.

Regards,

Ben

[gto]

Hello tonydm !

The master/detail relation will work if you do that:

Code: Select all

   [master query] ------ [data source]
                              |
                              |
                              |
                        [detail query]

Understanding

The master query you will setup normally, as you want, using filters, SQL or anything you like.
After that, you will put a TDataSource (yes, that which comes with delphi) and link the DataSet property to your master query.
And finally, create the detail query. Before typing the SQL script, set the DataSource property to the DataSource of the master query. Now, all the fields from the master become parameters to your SQL!
Imagine something link this:

You have a database with a "clients" table. Between client data you have a field "number", with an unique number. That number is the index to another table, "buys". In that table, there are many records, but you only want to fetch the records where buys.clientID match your actual client.
Pratically:

Code: Select all

-  clients -

name   number

linus    1
bill     2
steve    3

- buys -

clientID  sum
  1      25,57   => linus
  3      19,65   => steve
  1      50,49   => linus
  1      200,00  => linus
  2      16,78   => bill
  2      95,10   => bill

Linus has 3 buys, bill 2 and steve only 1.
Building the master/detail:

One master query:

select c.name, c.number from clients c order by name

One DataSource, pointing to master query.

One detail query, linked to the DataSource:

select b.clientID, b.sum from buys b where b.clientID = :number

Take a closer look to the detail SQL. It's a normal select statement, but the where clause work with :number parameter, comming direct from the DataSource (which is linked to MasterQuery). Every single column fetched by the master query, become parameters in the Detail SQL.

[tonydm]

Thanks Ben and GTO.

I had resorted to your suggestion Ben which I wanted to avoid. The reason being that it required me to manually handle the key and mouse actions withing the grid and call an update routine to change the detail resultset. I wanted the detail change relative to the master view to be automatically updated as the user moved about in the master dbgrid. Your suggestion GTO looks interesting. I'll give it a try. Thanks very much to both of you!

Tony