Combine two datatables in one datatable using select query

as far as I know , dataset does not support SELECT-SQL, you could use LINQ but why not initially SELECT from the datasource

ex:
Select * from table1 --> Fill datatable1 in dataset
Select * from table2 --> Fill datatable2 in dataset
Select * from table1, table2 where  table1.key=table2,key --> Fill datatable3 in dataset
 

Best Regards,

Yes I want to use SQL select.

In fact I have these select queries which work in access.

1.Select query for the first datatable:
SELECT NALOG1.GBRV, NALOG1.DATA, Min(IIf([NALOG1.GBRV]=[@gbr1] And ((NOVI.AB)=[@ab1] Or (NOVI.AB)=[@ab2]) And [NALOG1].[POCKM]>0,[NALOG1.POCKM],Null)) AS pockm, Max(IIf([NALOG1.GBRV]=[@gbr1] And ((NOVI.AB)=[@ab1] Or (NOVI.AB)=[@ab2]) And ([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]),[NALOG1.KRAJKM],Null)) AS krajkm, Max(NOVI.DATAP) AS Poslprov, Max(NOVI.DATAS) AS Poslserv, Sum(NALOG1.km) AS sumkm, Max(NOVI.KMP1) AS KMP, Max(NOVI.KMS1) AS KMS
FROM NALOG1 INNER JOIN NOVI ON NALOG1.GBRV = NOVI.GBR
GROUP BY NALOG1.GBRV, NALOG1.DATA
HAVING (((NALOG1.GBRV)=[@gbr1]) AND ((NALOG1.DATA)>=[@data1] And (NALOG1.DATA)<=[@data2]));


2. Select query for the second datatable:
SELECT MAGACIN.GBR, MAGACIN.DATA, NOVI.AB, Sum(IIf([sifra]='0992201',[kol],Null)) AS Addblue, Sum(IIf([sifra]='0999001',[kol],Null)) AS Antifriz, Sum(IIf([sifra]='0991000',[kol],Null)) AS Gorivo, Sum(IIf([sifra]='0993050',[kol],Null)) AS Motmaslo
FROM MAGACIN INNER JOIN NOVI ON MAGACIN.GBR = NOVI.GBR
GROUP BY MAGACIN.GBR, MAGACIN.DATA, NOVI.AB
HAVING (((MAGACIN.GBR)=[@gbr1]) AND ((NOVI.AB)=[@ab1] or (NOVI.AB)=[@ab2]) AND ((MAGACIN.DATA)>=[data1] And (MAGACIN.DATA)<=[data2]))
ORDER BY MAGACIN.DATA;


3. and the select query for the combination of theboth datatables:
SELECT NalogNov.DATA, MagNov.GBR, Max(IIf([MagNov].[DATA]=[NalogNov].[DATA],[MagNov].[Gorivo],0)) AS Gorivo1, Max(IIf([MagNov].[DATA]=[NalogNov].[DATA],[MagNov].[Addblue],0)) AS Addblue1, Max(IIf([MagNov].[DATA]=[NalogNov].[DATA],[MagNov].[Antifriz],0)) AS Antifriz1, Max(IIf([MagNov].[DATA]=[NalogNov].[DATA],[MagNov].[Motmaslo],0)) AS Motmaslo1, NalogNov.pockm, NalogNov.krajkm, [krajkm]-[pockm] AS RAZLIKA, Max(NalogNov.Poslprov) AS MaxOfPoslprov, Max(NalogNov.Poslserv) AS MaxOfPoslserv, Max(NalogNov.KMS) AS MaxOfKMS, Max(NalogNov.KMP) AS MaxOfKMP
FROM NalogNov LEFT JOIN MagNov ON NalogNov.GBRV = MagNov.GBR
WHERE (((MagNov.GBR)=[NalogNov].[GBRV]))
GROUP BY NalogNov.DATA, MagNov.GBR, NalogNov.pockm, NalogNov.krajkm, [krajkm]-[pockm]
ORDER BY NalogNov.DATA;

So I fill the first two datatables by using

oleDBDataAdapter1.SelectCommand = command1;
oleDBDataAdapter1.Fill(dataSet2, "NalogNov")

and respectively for the other datatable.

I don't know how to fill the third datatable.

Please try the following.

1) You can use datatable.merge to merge two datatable into one.

DataTable dt1 = new DataTable();

DataTable dt2 = new DataTable();

DataTable dt3 - dt1.Copy();

dt3.Merge(dt2);

http://msdn.microsoft.com/en-us/library/system.data.datatable.merge.aspx

2) You can import the data rows from different tables and load into one.

http://msdn.microsoft.com/en-us/library/system.data.datatable.loaddatarow(v=vs.100).aspx
http://msdn.microsoft.com/en-us/library/system.data.datatable.importrow(v=vs.100).aspx