ASP -> Access DB join-statements

Hoi,

Het gebruik van een JOIN-statement dmv "Microsoft.Jet.OLEDB.4.0" (asp)
naar Access blijft me tot frustrering toe foutmeldingen blijft geven.

Met een vrij verwarrende syntax (veel geneste haakjes) was het me
gelukt om 2 JOINS uit te voeren, maar een 3e lukte dan helemaal niet
meer. Het lijkt ook onmogelijk om duidelijk documentatie te vinden op
het net hoe dat M$ JOIN-statements ziet met deze setup. (ASP-> Access)

Dus, waarom werkt een dergelijke query als onder niet in ASP->Access?

----query----
SELECT * FROM table1 as T1
INNER JOIN table2 as T2 ON T1.needed = T2.id
LEFT OUTER JOIN table3 as T3 ON T1.someneeded = T3.id
WHERE T1.interesting = "this value"
---/query----

Om dit te laten werken, moet ik de query hieronder gebruiken. Welke het
doel mist van mijn OUTER JOIN; omdat als "someneeded" leeg is zal de
query mij geen resultaten uit T1 geven.

----query----
SELECT * FROM table1 as T1,table2 as T2,table3 as T3
WHERE T1.needed = T2.id AND T1.someneeded = T3.ID
AND T1.interesting = "this value"
---/query----

Een dergelijke join zou een performatie-dip betekenen tgo een degelijke
JOIN, heeft men me verteld.

Kan iemand dit voor mij verduidelijken en me in de juiste richting
wijzigen?
Alvast erg bedankt!
Tim.

Re: ASP -> Access DB join-statements

Tim.Vanlaere@gmail.com wrote:
> Hoi,
>
> Het gebruik van een JOIN-statement dmv "Microsoft.Jet.OLEDB.4.0" (asp)
> naar Access blijft me tot frustrering toe foutmeldingen blijft geven.
>
> Met een vrij verwarrende syntax (veel geneste haakjes) was het me
> gelukt om 2 JOINS uit te voeren, maar een 3e lukte dan helemaal niet
> meer. Het lijkt ook onmogelijk om duidelijk documentatie te vinden op
> het net hoe dat M$ JOIN-statements ziet met deze setup. (ASP-> Access)
>
> Dus, waarom werkt een dergelijke query als onder niet in ASP->Access?
>
> ----query----
> SELECT * FROM table1 as T1
> INNER JOIN table2 as T2 ON T1.needed = T2.id
> LEFT OUTER JOIN table3 as T3 ON T1.someneeded = T3.id
> WHERE T1.interesting = "this value"
> ---/query----

I don't understand Dutch (I think that's Dutch - Evertjian, help me out
....), but this query will fail in Access because the joins are not nested
using parentheses. This should work:

SELECT * FROM (table1 as T1
INNER JOIN table2 as T2 ON T1.needed = T2.id)
LEFT OUTER JOIN table3 as T3 ON T1.someneeded = T3.id
WHERE T1.interesting = "this value"


Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ASP -> Access DB join-statements

Thank you Bob,

I actually translated my initial English message into Dutch cause
google groups asked me to tag my post with a language, and failing to
find "English" as an option I felt forced to submit in Dutch. I
appoligize for any inconvenience.

My problem is that I can't easily make joins, where I've been able to
join two tables using asp->Access, but more joins just fail miserably.

In all honesty, I'm not sure how the parenthesis should be used with
the Jet OLEDB, and failed to find proper documentation about this
subject. (I've seen alot of very confusing join queries which just
didn't seem to work when I tried to modify to my case.)

In the sollution you offer I'm confused as how to add yet another
table. (or multiple others). I'd conclude something like the following
query out of your example. To this point I've tried alot and would like
to understand it properly instead of evading my problem...

SELECT * FROM ((table1 as T1
INNER JOIN table2 as T2 ON T1.needed = T2.id)
INNER JOIN table3 as T3 ON T1.someth = T3.id)
LEFT OUTER JOIN table3 as T3 ON T1.someneeded = T3.id
WHERE T1.interesting = "this value"

Thanks alot for your time!
Tim

Re: ASP -> Access DB join-statements

Tim.Vanlaere@gmail.com wrote:
> My problem is that I can't easily make joins, where I've been able to
> join two tables using asp->Access, but more joins just fail miserably.
>
> In all honesty, I'm not sure how the parenthesis should be used with
> the Jet OLEDB, and failed to find proper documentation about this
> subject. (I've seen alot of very confusing join queries which just
> didn't seem to work when I tried to modify to my case.)

I understand completely. It took me a long time to get it, and frankly, I
still have to rely on the query builder at times, especially since I've
switched from using Access to using SQL Server where this is not an issue.

>
> In the sollution you offer I'm confused as how to add yet another
> table. (or multiple others). I'd conclude something like the following
> query out of your example. To this point I've tried alot and would
> like to understand it properly instead of evading my problem...
>
> SELECT * FROM ((table1 as T1
> INNER JOIN table2 as T2 ON T1.needed = T2.id)
> INNER JOIN table3 as T3 ON T1.someth = T3.id)
> LEFT OUTER JOIN table3 as T3 ON T1.someneeded = T3.id
> WHERE T1.interesting = "this value"
>
> Thanks alot for your time!
> Tim

Use the Access Query Builder. Open your database in Access, switch to the
Queries tab, create a new query in design View, add the tables to the
window, use the mouse to create the joins by clicking and dragging. Then
switch to SQL View to see where the parentheses are supposed to go. Test the
query to verify it does what you want.

At this point, if I was doing it, I would be parameterizing and saving this
query. See these links:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/b3d322b882a604bd

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl


Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ASP -> Access DB join-statements

Thanks for the pointers!
I really appreaciate it :)

Tim

Bob Barrows [MVP] schreef:
> Tim.Vanlaere@gmail.com wrote:
> > My problem is that I can't easily make joins, where I've been able
to
> > join two tables using asp->Access, but more joins just fail
miserably.
> >
> > In all honesty, I'm not sure how the parenthesis should be used
with
> > the Jet OLEDB, and failed to find proper documentation about this
> > subject. (I've seen alot of very confusing join queries which just
> > didn't seem to work when I tried to modify to my case.)
>
> I understand completely. It took me a long time to get it, and
frankly, I
> still have to rely on the query builder at times, especially since
I've
> switched from using Access to using SQL Server where this is not an
issue.
>
> >
> > In the sollution you offer I'm confused as how to add yet another
> > table. (or multiple others). I'd conclude something like the
following
> > query out of your example. To this point I've tried alot and would
> > like to understand it properly instead of evading my problem...
> >
> > SELECT * FROM ((table1 as T1
> > INNER JOIN table2 as T2 ON T1.needed = T2.id)
> > INNER JOIN table3 as T3 ON T1.someth = T3.id)
> > LEFT OUTER JOIN table3 as T3 ON T1.someneeded = T3.id
> > WHERE T1.interesting = "this value"
> >
> > Thanks alot for your time!
> > Tim
>
> Use the Access Query Builder. Open your database in Access, switch to
the
> Queries tab, create a new query in design View, add the tables to the

> window, use the mouse to create the joins by clicking and dragging.
Then
> switch to SQL View to see where the parentheses are supposed to go.
Test the
> query to verify it does what you want.
>
> At this point, if I was doing it, I would be parameterizing and
saving this
> query. See these links:
>
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/b3d322b882a604bd
>
>
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so
I
> don't check it very often. If you must reply off-line, then remove
the
> "NO SPAM"

Re: ASP -> Access DB join-statements

Bob Barrows [MVP] wrote on 20 mei 2005 in
microsoft.public.inetserver.asp.db:

> I don't understand Dutch (I think that's Dutch - Evertjian, help me out
> ...)

Sorry Bob, I was organizing g.p. doctors protest against governments plans,
using serverside procedures. The language problem seems to be resolved.

--
Evertjan.
The Netherlands.
(Replace all crosses with dots in my emailaddress)