ParanoidOne
asked on
Oledbdataadapter, doing a left outer join give an error
How can I accomplish a left outer join with the Oledbdatadapter, I get an error when the statement is run !
Carl Tawn
What error ? Whats the code ?
ASKER
I coughs up an error as soon as you leave the Query Designer, basically saying the statement will not work. I tried the same statement using the sqldataadapter and it does not work. It is something about Foxpro and the ODBC and OLEDB not allowing a left outer join.
ASKER
I am sorry, type there. the SQLDataAdapter DOES allow the statement.
What type of database are you accessing?
Bob
Bob
ASKER
It is a set of DBF files, a directory containing about 10 of them. They are either Informix or Foxpro but normally the Foxpro setup seems to work fine, something like the OLEDbDataAdapter
Are you using the Visual FoxPro OleDb provider?
Bob
Bob
ASKER
Yeah I have used both.
What does the SQL statement look like?
Bob
Bob
ASKER
Something like this, but the field names being different of course. This simple statement will run on the SQL adapter but not with the Oledb. The error is "Command contains unrecognized phrase/keyword.
SELECT table1.id, table1.type, table2.descript
FROM { oj table1 LEFT OUTER JOIN
table2 ON table1.type = table2.type }
SELECT table1.id, table1.type, table2.descript
FROM { oj table1 LEFT OUTER JOIN
table2 ON table1.type = table2.type }
Did you try it like this:
SELECT table1.id, table1.type, table2.descript
FROM table1 LEFT OUTER JOIN
table2 ON table1.type = table2.type
Bob
SELECT table1.id, table1.type, table2.descript
FROM table1 LEFT OUTER JOIN
table2 ON table1.type = table2.type
Bob
ASKER
THe designer will pop the other characters back into the statement and still pop out an error.
Does it run in the designer with those characters?
Bob
Bob
ASKER
No.
I'm sorry, I meant to say, "Does it run in the designer without those characters?"
Bob
Bob
ASKER
Nope, I tried that too.
ASKER
BTW I think it will require a UNION to work. From some reading Foxpro does not allow a LEFT OUTER JOIN
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is the website I read it on. Unfortunately his example does not seem to work for me, well it does work just not as intended for my purposes. I am still trying some stuff though.
What kind of problems are you having with the UNION statement?
Bob
Bob
ASKER
It appears to only give the information that is in both tables, instead of all of the information on the left. The end result I wanted was a left outer join.
That's where this comes into play:
SELECT a.keyfield,;
a.datafield1,;
000.00,;
000.00;
FROM a;
WHERE a.keyfield NOT IN;
(SELECT b.keyfield;
FROM b)
Give me everything that is not in table b.
Bob
SELECT a.keyfield,;
a.datafield1,;
000.00,;
000.00;
FROM a;
WHERE a.keyfield NOT IN;
(SELECT b.keyfield;
FROM b)
Give me everything that is not in table b.
Bob