I have to join 2 or more dataset in one larger

Question

I am using delphi and I would like to join 2 or more datasets dynamically in one larger . I am going to show the result of the merge in a dbgrid .

What would be the best solution ?

Thank you in advance

Answer 1

Using Sql Server you can construct a SQL query in its TransactSQL which uses the JOIN construct to "connect" two tables together. See for example this previous answer:SQL Server: Multiple table joins with a WHERE clause.

You should take a look at the TransactSQL documentation on MSDN, e.g.

https://msdn.microsoft.com/en-us/library/zt8wzxy4.aspx.

The following is an example to retrieve authors and their titles from the demo Pubs database for Sql Server:

use pubs
select
  a.au_id, 
  a.au_fname,
  a.au_lname,
  t.title
from
  authors a
  join titleauthor x on a.au_id = x.au_id
  join titles t on x.title_id = t.title_id
order by
  a.au_lname, a.au_fname

You can execute it to try it out in SqlServer Management Studio by using New Query, then try pasting it into a TAdoQuery in a Delphi project.

A good way to visualise the result of a JOIN is to use a TBGrid. However, a JOIN is not always a good thing to use if you are wanting to edit the contents of one or more of its tables. For example, a business task might be to edit details of an author's books. To do that, a more convenient was than a JOIN might be to connect the Authors and Titles tables in your app, using AdoQuerys, by setting up a so-called Master-Detail relationship between them. There are various ways to do that and maybe you best bet would be to google a Delphi tutorial about how to set up and use them.

In SQL, the UNION construct is another way of getting data from two separate tables.

Btw Ado components, e.g. TAdoQuery, will work fine for what you want to do. If you have a version of Delphi, you could try that, too, but it's maybe a bit more daunting if you've no done this type of thing before.

Update You've said in comments that you want to get data from two servers to end up in one dataset without using Sql Server's "linked servers" method.

To do this is quite straightforward: it takes a bit of setting up but the merging of the data can be done in two lines of code. Here's how:

1. For each server, place the following on your form/datamodule: An AdoConnection, AdoQuery, DataSetProvider and ClientDataSet. Configure the AdoConnection to connect to one of the servers, set the AdoQuery to use the AdoConnection, and set the DataSet property of the DataSetProvider to the AdoQuery. Set the ProviderName property of the ClientDataSet to the DataSetProvider. Add a DataSource and set its DataSet property to the ClientDataSet and a DBGrid connected to the DataSource. Give these component's names A and B suffixes.

Using the Pubs database example, set the AdoQuery's SQL.Text property to

  select
    [Source] = 'ServerA',  -- and ServerB for the other one of course
    *
  from
    authors

2. Check that both sets of components are working by setting the two CDS's Active property to True. In the Form's FormCreate event, add code to open both CDSs.

3. Add a third ClientDataSet, 'cdsMergedData' to the form and give it its own DataSource and DBGrid. Set cdsMergedData's IndexFieldNames to 'au_lname;au_fname'.

4. Put a TButton 'btnMerge' on the form and add the following code to its OnClick event :

   cdsMergedData.Data := cdsServerA.Data;

   cdsMergedData.AppendData(cdsServerB.Data, True);

and that's it. The True argument to AppendData tells cdsMergedData that there is no more data to load. If you wanted to add data from a third server, you would set it to False for cdsServerB and True for cdsServerC.

There is no code-free way of feeding data changes made in cdsMergedData back to the source server. You would need to copy the changes to the counterpart row in the relevant cdsServer CDS, and then call ApplyUpdates on it.

Answer 2

You could try this : FIREDAC LOCALSQL

It works like a charm. but pay attention to

1. I'm not sure if it works for XE7. You have to check it. In Seatle it is available.
2. Can't recommend it for huge data. 3-4 thousand records may come to be slow.
3. I know about one bug. when making SUM(FIELD) and use Group by by some reason sum come to be string field.