SQL Server 2008 Master/Detail ( Using Delphi and ADOQuery )

Question

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 !

Answer 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 

Answer 2

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;

Answer 3

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.