1

I have a SQL Server 2008 DataBase with 2 Table : Master and Detail

Master Fields : ID and ... ;

Detail Fields : ID , MasterID and ... ;

I have been set the relationship between Master and Detail in SQL Server

I want when I Select a record in Master DBGrid , Detail Query returns only records related to selected Master Record and Detail DBGrid Show only Related records

I dont want to Requery Detail ADOQuery everytime I select a Master Record with such as SQL Code ( for Example ) :

SELECT * FROM Detail WHERE MasterID = Master.ID

how can i do this without using ADOTable ( Master Source ) !?

in other words i want this Relationship be in DB Layer !

Mahmoud_Mehri
  • 1,643
  • 2
  • 20
  • 38
  • 2
    as I know, master-detail relations are commonly used with `TTable` descendant compoents. Do you actually need `TADOQuery` instead of `TADOTable`, which has `MasterSource` property? – teran Dec 26 '13 at 10:03
  • 2
    "I dont want to Requery Detail ADOQuery everytime ..." Why not? If Sql Server is the back-end, any other type of dataset you might use is going to have to do that under the hood anyway. – MartynA Dec 26 '13 at 13:11
  • "in other words i want this Relationship be in DB Layer !" Then set up a stored procedure (or table-UDF) on the server that takes the MasterID as input and returns the detail records. Your Delphi app is still going to have to invoke the stored proc/function, though. – MartynA Dec 26 '13 at 14:21

3 Answers3

1

If you want to flatten the master detail relationship, you can do a JOIN query to obtain all data from the 2 tables:

SELECT m.*, d.* FROM Master m INNER JOIN Detail d ON m.Id = d.MasterId
whosrdaddy
  • 11,720
  • 4
  • 50
  • 99
1

You just need to set up detail DataSet as usual and change the SQL to a parameterized one. SELECT * FROM details WHERE MasterID =: id

This way only the corresponding details are loaded for each master record, which will throttle the load and keep displayed data more actual.

enter image description here

bummi
  • 27,123
  • 14
  • 62
  • 101
0

You can use filter propery of Tadoquery on detail table.

add this code to AfterScroll event of AdoQueryMaster;

AdoQueryDetail.filter := 'Where MasterID = ' +inttostr(AdoQueryMaster.fieldbyname('ID').asinteger);
AdoQueryDetail.filtered := true;
sddk
  • 1,115
  • 1
  • 10
  • 20