| Joining to the same table twice from different fields [message #370991] |
Mon, 31 July 2000 07:40  |
Simon Delicato
Messages: 4
Registered: July 2000
|
Junior Member |
|
|
Hi,
I have been having trouble joining a table to the same table twice and am now wondering if it is possible.
problem:
I have 3 different tables A, B and C
B is joined to C (works fine)
B is joined to A and C (works fine)
When I try and join B to C and also to A1 and A2 (alaises of A) then a syntax error occurs.
I am joining on two different fields in B with the same type as the field in A.
Any help would be appreciated,
Thanks,
Simon
|
|
|
|
| Re: Joining to the same table twice from different fields [message #370996 is a reply to message #370991] |
Wed, 02 August 2000 05:19  |
Vjeko
Messages: 15
Registered: August 2000
|
Junior Member |
|
|
It should work if joined data types are the same. I don't know what's wrong in your statement, but I'll give you one similar example that works fine.
SELECT a.field1,a.field2,a.field3,b.field2,c1.field3,c2.field3
FROM TableA a,TableB b,TableC c1,TableC c2
WHERE a.field1=b.field1 and a.field2=c1.field3 and a.field3=c2.field3;
|
|
|
|
| Re: Joining to the same table twice from different fields [message #370998 is a reply to message #370991] |
Thu, 03 August 2000 05:55 |
Vjeko
Messages: 15
Registered: August 2000
|
Junior Member |
|
|
I can't see any mistake in your statement. About number of joins - 9 is fine (also 10 and more...).
You said error 3146 appears. But in MS Access manual they describe error 3146 as :
"ODBC – call failed. (Error 3146)
Using an ODBC connection, you tried to perform an operation on data in an ODBC data source. This error may occur when the ODBC data source is on a network drive and you aren't connected to the network. Make sure the network is available, and then try the operation again. "
On the other hand, you said that all works fine if C2 is removed...
Sorry...
P.S. Why are you not using OracleDeveloper instead?
|
|
|
|
| Re: Joining to the same table twice from different fields [message #370999 is a reply to message #370998] |
Thu, 03 August 2000 06:12 |
Simon Delicato
Messages: 4
Registered: July 2000
|
Junior Member |
|
|
Hi,
Thanks for your help.
It isn't a network problem as Access reports - everything else works fine.
I'm a student on a summer placement at the moment and my project is to construct reports from a DB using Access. They maybe thought it would be better for me to work with that as opposed to OracleDeveloper - I don't know.
Everything works fine if I use the MS Jet engine, but I'm trying to optimise the queries by passing SQL through to the back end.
Getting it to work is not essential as when I tested the SQL performance without C2 included, against the Jet performance with C2 included there was only a 10% difference. This difference would probably disappear (or even become negative) when / if I got it to work.
Thanks a lot for your help anyway.
Simon
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
