ON ... AND statement problem

O

Olivier57

Hi,

I'm not very good in SQL and encounter a problem. I hope someone can help.
In my following query, I try to use an ON Statement withe a condition, that's
to say :

ON CABLE.IDENTIFIANT=RCABLE_NDS.ID_CABLE AND RCABLE_NDS.CATEGORIE=1

Access says that it doesn't support this join but if I had brackets like
this it's ok

ON ((CABLE.IDENTIFIANT=RCABLE_NDS.ID_CABLE) AND (CABLE_NDS.CATEGORIE=1))

but when I save the query, Access take of the more enclousing brackets

(CABLE.IDENTIFIANT=RCABLE_NDS.ID_CABLE) AND (RCABLE_NDS.CATEGORIE=1)

And i I try to execute this, I get the same message. So I have to add again
brackets

((CABLE.IDENTIFIANT=RCABLE_NDS.ID_CABLE) AND (RCABLE_NDS.CATEGORIE=1))

And it's ok.

So, I'm wondering. Why does Access takes those brackets off ? Am I wrong in
my On ... AND... statement ? Why does it accept it then with the brackets ?

I also try to write with WHERE statement,as Access does with creation mode
(withe plenty of brackets !!) but it doesn't work.

ON (CABLE.IDENTIFIANT=RCABLE_NDS.ID_CABLE) WHERE (RCABLE_NDS.CATEGORIE=1)

Thanks so much for your answer.



INSERT INTO NTK_ROP_RESULT
SELECT NTK_ROP_BASE.STATUT AS ROUTE_STATUT, NTK_ROP_BASE.ROUTE AS
ROUTE_NUMERO, NTK_ROP_BASE.ORDRE AS ROUTE_ORDRE, NTK_ROP_BASE.ID_FIBRE AS
FIBRE_ID, CABLE.IDENTIFIANT AS CABLE_ID, CABLE.NOM AS CABLE_NOM,
RCABLE_NDS.ID_NDS AS DEPART_CABLE, PT_TECH.NOM AS PT_NOM
FROM NTK_ROP_BASE INNER JOIN (FIBRE INNER JOIN (CABLE INNER JOIN (RCABLE_NDS
INNER JOIN PT_TECH ON RCABLE_NDS.ID_NDS=PT_TECH.IDENTIFIANT) ON
CABLE.IDENTIFIANT=RCABLE_NDS.ID_CABLE AND RCABLE_NDS.CATEGORIE=1) ON
FIBRE.ID_CABLE=CABLE.IDENTIFIANT) ON NTK_ROP_BASE.ID_FIBRE=FIBRE.IDENTIFIANT
ORDER BY NTK_ROP_BASE.ROUTE, NTK_ROP_BASE.ORDRE;
 
O

Olivier57

By the way, when I have a look to Access help I notice that the example uses
closing ) and ] but no opening ones ! So my syntax seems to be correct.

Access example "INNER JOIN"

SELECT champs
FROM table1 INNER JOIN table2
ON table1.champ1 oprcmp table2.champ1 AND
ON table1.champ2 oprcmp table2.champ2) OR
ON table1.champ3 oprcmp table2.champ3)];
 
J

John W. Vinson/MVP

Olivier57 said:
Hi,

I'm not very good in SQL and encounter a problem. I hope someone can help.
In my following query, I try to use an ON Statement withe a condition,
that's
to say :

ON CABLE.IDENTIFIANT=RCABLE_NDS.ID_CABLE AND RCABLE_NDS.CATEGORIE=1

Why are you putting the *condition* in the ON clause of the JOIN? Ordinarily
you would apply a condition in the WHERE clause, since it has nothing to do
with joining the two tables.
 
O

Olivier57

Hi,

Thanks for your answer. At first, it's possible that I do not make enough
difference between the processing of ON and WHERE statement, i do not have a
long experience with SQL.

I use it Because
- examples shows that it's possible to add condition in the on statement
- it works with enough brackets.
- RCABLE_NDS.CATEGORIE cab be 1, 2, 3 and I thank it was better to join
directly with the good value in the table.
- Finally because my different tests with WHERE gave me "syntax error" and
then back to the two first point. Obviously, if I do not use properly the ON
clause I would try something else but would you be so kind to explain why,
according to those two same points ?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top