I access an xls-document via oledb with c#. I have to join two sheets without any on-condition. I try to get any column-combination which is possible from those two sheets.
Under mssql I can do it using ON 1 = 1 for the join-condition, but that doesn't work here.
Here are some variations i have already tried:
1. SELECT [Grid 1$].[FOO], [Grid 2$].[BAR] FROM [Grid 1$] LEFT JOIN
[Grid 2$] ON 1 = 1
2. SELECT [Grid 1$].[FOO], [Grid 2$].[BAR] FROM [Grid 1$] LEFT JOIN
[Grid 2$] ON TRUE
3. SELECT [Grid 1$].[FOO], [Grid 2$].[BAR] FROM [Grid 1$] CROSS JOIN
[Grid 2$]
Number 1 and 2 throw:
System.Data.OleDb.OleDbException (0x80040E14): JOIN expression not supported.
Number 3 throws:
System.Data.OleDb.OleDbException (0x80004005): Fehler E_FAIL(0x80004005) in IErrorInfo.GetDescription.
Is there any solution or workaround for this problem?
To point out what i am exactly looking for here is an example:
Sheet 1:
1. foo
2. bar
Sheet 2:
1. jon skeet
2. harry potter
The query should return:
foo jon skeet
foo harry potter
bar jon skeet
bar harry potter
I hope this points out my intent.
Having a LEFT JOIN with a 1=1 condition is pointless. From your explanation you are after a cartesian join (CROSS JOIN and FULL JOIN on some databases). You can do it without any join clause (old style join IOW):
SELECT [Grid 1$].[FOO], [Grid 2$].[BAR] FROM [Grid 1$], [Grid 2$]
Note: And always keep in mind that Excel is not a database.