Delphi select line in a dbgrid and show other lines in other dbgrid

Question

I have 2 dbgrid which are connected to two different SQL databases. One is "provider" table which coloumns are (provider_name, provider_adress), other table is customer table with (customer_name, customer_adress, provider_name). More customers can belong to one provider. The common field is the provider's name.

My question is... I want to list the customer names belongins to providers. If i click on a random 'provider_name' in a dbgrid, the customer table dbgrid will be active and will show the customer_names, adresses etc..) I use AdoDatasets and AdoQueries.

Select * from customer_name, customer_adress --(in customer table) 
where provider name = 'xy' --(in provider table) 

Anyone can help in a query? Anyone can help me how can i do it?Thanks for the answers!

Answer 1

Use a separate query for your customer table, with SQL something like this (replace CustQuery with the name of your own ADOQuery component, and the table and column names for your actual table information, of course):

CustQuery.SQL.Text := 'SELECT c.customer_name, c.customer_address' + #13 +
                      'FROM customer c' + #13 +
                      'WHERE c.provider_name = :provider';

In the provider query's AfterScroll event handler, use code something like this (generate it from double-clicking the AfterScroll event in the Object Inspector Events tab):

procedure TForm1.ProviderQueryAfterScroll(DataSet: TDataSet);
begin
  CustQuery.DisableControls;
  try
    CustQuery.Close;
    CustQuery.Paramseters.ParamByName('provider').Value :=
      ProviderQuery.FieldByname('Provider_Name').Value;
    CustQuery.Open;
  finally
    CustQuery.EnableControls;
  end;
  CustQuery.Open;
end;

The above will work whether the user clicks a new row in the provider grid or uses the keyboard to scroll through rows.