I am querying a Dbf file and I want to full join two query in C# console application. But It seems that Full Join is not supported in Microsoft.jet.oledb.4.0. I get the Following error when I run the query.
IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
Here are the Table Details and the desired query Behaviour.
Purchase and Sales transactions are stored in the Table Mtrans.DBF. And the field It_type is used to distinguish between Purchase transaction and Sales Transaction.I want to combine both sales and Purchase quantities of an item in a single row.
But the query runs smooth without any error if i use left or inner or right join instead of Full join. Please Help me in this. I request the experts here to throw some lights on if there are any workaround for this error.
Here is my Query EXpresision
OleDbDataAdapter da = new OleDbDataAdapter();
da = new OleDbDataAdapter("select purtran.it_name,purtran.it_code,
purtran.purcqnty,purtran.puruqnty,saltran.cqnty,
saltran.uqnty,saltran.avalue from
(select first(it_name) as
it_name,mtrans.it_code,sum(cqnty) as purcqnty,sum(uqnty)
as puruqnty
from mtrans
where date >=#" + fdt + "# and
date <=#" + tdt + "# and (voucher is null or len(voucher) =0)
and it_type = '01'
group by it_code) as purtran
full join
(select it_code,sum(cqnty) as cqnty,
sum(uqnty) as uqnty,sum(avalue) as avalue,first(tp1) as tp1
from mtrans
where date >=#" + fdt + "# and date <=#" + tdt + "#
and (voucher is null or len(voucher) = 0) and
it_type = '02' group by it_code)
saltran
on saltran.it_code = purtran.it_code ", con);
da.Fill(dt);
No. Access does not support FULL JOIN. You need to do a LEFT JOIN and combine the results using UNION ALL with a RIGHT JOIN. See this tutorial .