Board index » VB & Database

All times are UTC



 SELECT . INNER JOIN--INNER JOIN 
Author Message
Klaus Ecker
#1 / 7
 SELECT . INNER JOIN--INNER JOIN

Hi everyone,

I use VB6,
ACCESS-Database 2000  with
tables    Fields
ta1         Name                GrupID     ZubID
ta2         Gruppe              ID2
ta3         Zubereitung      ID3

My problem is to combine the two INNER JOINs,
each INNER JOIN separately works fine.

When I use the SQL(below)
the following ERROR occurs:
ERROR: 2147217900(80040e14)
Syntax Error: missing operator in:
 'ta1.ZubID = ta3.ID3 INNER JOIN ta2 ON ta1.GrupID = ta2.ID2'

What "operator" is missing here?

Thanks in anticipation
Klaus

Set con = New ADODB.Connection
       con.Provider = "Microsoft.Jet.OLEDB.4.0"
       con.CursorLocation = adUseClient
       con.Open "Data Source=" & Daba

SQL = "Select ta1.Name, ta2.Gruppe, ta3.Zubereitung " & _
             "FROM ta1 " & _
             "INNER JOIN ta2 ON ta1.GrupID = ta2.ID2" & _
             "INNER JOIN ta3 ON ta1.ZubID = ta3.ID3; "



Fri, 06 Dec 2002 03:00:00 GMT  
Shelby Cai
#2 / 7
 SELECT . INNER JOIN--INNER JOIN


Quote:
> SQL = "Select ta1.Name, ta2.Gruppe, ta3.Zubereitung " & _
>              "FROM ta1 " & _
>              "INNER JOIN ta2 ON ta1.GrupID = ta2.ID2" & _
>              "INNER JOIN ta3 ON ta1.ZubID = ta3.ID3; "

One thing the could be wrong is the lack of a space after ta2.ID2 in your
first join.  The resultant string would read something like:

INNER JOIN ta2 ON ta1.GrupID = ta2.ID2INNER JOIN ta3 ON ta1.ZubID = ta3.ID3;

instead of what you are expecting...

Regards,

Shelby Cain



Fri, 06 Dec 2002 03:00:00 GMT  
Klaus Ecker
#3 / 7
 SELECT . INNER JOIN--INNER JOIN
Thank you Shelby for your hint.
Unfortunately the missing blanc was only in my email, not in my program.
The following (corrected) SQL produces the error:

SQL = "Select ta1.Name, ta2.Gruppe, ta3.Zubereitung " & _
          "FROM ta1 " & _
          "INNER JOIN ta2 ON ta1.GrupID = ta2.ID2 " & _
          "INNER JOIN ta3 ON ta1.ZubID = ta3.ID3;"

 ERROR: 2147217900(80040e14)
   Syntax Error: missing operator in:
   'ta1.GrupID = ta2.ID2 INNER JOIN ta3 ON ta1.ZubID = ta3.ID3'

Each one of the INNER JOINs  separately works fine
only the combination failes.
Do you have another idea?

Regards
Klaus Eckert



Quote:



> > SQL = "Select ta1.Name, ta2.Gruppe, ta3.Zubereitung " & _
> >              "FROM ta1 " & _
> >              "INNER JOIN ta2 ON ta1.GrupID = ta2.ID2" & _
> >              "INNER JOIN ta3 ON ta1.ZubID = ta3.ID3; "

> One thing the could be wrong is the lack of a space after ta2.ID2 in your
> first join.  The resultant string would read something like:

> INNER JOIN ta2 ON ta1.GrupID = ta2.ID2INNER JOIN ta3 ON ta1.ZubID =
ta3.ID3;

> instead of what you are expecting...

> Regards,

> Shelby Cain



Sat, 07 Dec 2002 03:00:00 GMT  
Shelby Cai
#4 / 7
 SELECT . INNER JOIN--INNER JOIN
I'm not a SQL expert but that syntax doesn't appear to be a valid SQL
command (at least for Oracle) and it reminds me of SQL that MS Access spits
out when you view one of its queries in SQL mode.  It could be that you are
missing parenthesis around your conditions.... I remember Access being
extremely picky....

The proper SQL for your query (which I believe will still work in Access)
is:
"select ta1.name, ta2.gruppe, ta3.zubereitung from ta1, ta2, ta3 where
ta1.GrupID = ta2.ID2 and ta1.ZubID = ta3.ID3;"

Where the AND's are implicit inner joins....

If all else fails -- and I've done this many times wrestling with Access...
use the design query and do your query graphically.. then switch to the SQL
view to see what it gives you.

Regards,

Shelby Cain


Quote:
> Thank you Shelby for your hint.
> Unfortunately the missing blanc was only in my email, not in my program.
> The following (corrected) SQL produces the error:

> SQL = "Select ta1.Name, ta2.Gruppe, ta3.Zubereitung " & _
>           "FROM ta1 " & _
>           "INNER JOIN ta2 ON ta1.GrupID = ta2.ID2 " & _
>           "INNER JOIN ta3 ON ta1.ZubID = ta3.ID3;"

>  ERROR: 2147217900(80040e14)
>    Syntax Error: missing operator in:
>    'ta1.GrupID = ta2.ID2 INNER JOIN ta3 ON ta1.ZubID = ta3.ID3'

> Each one of the INNER JOINs  separately works fine
> only the combination failes.
> Do you have another idea?

> Regards
> Klaus Eckert





> > > SQL = "Select ta1.Name, ta2.Gruppe, ta3.Zubereitung " & _
> > >              "FROM ta1 " & _
> > >              "INNER JOIN ta2 ON ta1.GrupID = ta2.ID2" & _
> > >              "INNER JOIN ta3 ON ta1.ZubID = ta3.ID3; "

> > One thing the could be wrong is the lack of a space after ta2.ID2 in
your
> > first join.  The resultant string would read something like:

> > INNER JOIN ta2 ON ta1.GrupID = ta2.ID2INNER JOIN ta3 ON ta1.ZubID =
> ta3.ID3;

> > instead of what you are expecting...

> > Regards,

> > Shelby Cain



Sat, 07 Dec 2002 03:00:00 GMT  
Shelby Cai
#5 / 7
 SELECT . INNER JOIN--INNER JOIN
Sorry -- didn't see the "using Access 2000... " statement in plain site...
:)


Quote:
> Thank you Shelby for your hint.
> Unfortunately the missing blanc was only in my email, not in my program.
> The following (corrected) SQL produces the error:

> SQL = "Select ta1.Name, ta2.Gruppe, ta3.Zubereitung " & _
>           "FROM ta1 " & _
>           "INNER JOIN ta2 ON ta1.GrupID = ta2.ID2 " & _
>           "INNER JOIN ta3 ON ta1.ZubID = ta3.ID3;"

>  ERROR: 2147217900(80040e14)
>    Syntax Error: missing operator in:
>    'ta1.GrupID = ta2.ID2 INNER JOIN ta3 ON ta1.ZubID = ta3.ID3'

> Each one of the INNER JOINs  separately works fine
> only the combination failes.
> Do you have another idea?

> Regards
> Klaus Eckert





> > > SQL = "Select ta1.Name, ta2.Gruppe, ta3.Zubereitung " & _
> > >              "FROM ta1 " & _
> > >              "INNER JOIN ta2 ON ta1.GrupID = ta2.ID2" & _
> > >              "INNER JOIN ta3 ON ta1.ZubID = ta3.ID3; "

> > One thing the could be wrong is the lack of a space after ta2.ID2 in
your
> > first join.  The resultant string would read something like:

> > INNER JOIN ta2 ON ta1.GrupID = ta2.ID2INNER JOIN ta3 ON ta1.ZubID =
> ta3.ID3;

> > instead of what you are expecting...

> > Regards,

> > Shelby Cain



Sat, 07 Dec 2002 03:00:00 GMT  
Klaus Ecker
#6 / 7
 SELECT . INNER JOIN--INNER JOIN


Quote:
> I'm not a SQL expert but that syntax doesn't appear to be a valid SQL
> command (at least for Oracle) and it reminds me of SQL that MS Access
spits
> out when you view one of its queries in SQL mode.  It could be that you
are
> missing parenthesis around your conditions.... I remember Access being
> extremely picky....

> The proper SQL for your query (which I believe will still work in Access)
> is:
> "select ta1.name, ta2.gruppe, ta3.zubereitung from ta1, ta2, ta3 where
> ta1.GrupID = ta2.ID2 and ta1.ZubID = ta3.ID3;"

> Where the AND's are implicit inner joins....

> If all else fails -- and I've done this many times wrestling with
Access...
> use the design query and do your query graphically.. then switch to the
SQL
> view to see what it gives you.

> Regards,

> Shelby Cain



> > Thank you Shelby for your hint.
> > Unfortunately the missing blanc was only in my email, not in my program.
> > The following (corrected) SQL produces the error:

> > SQL = "Select ta1.Name, ta2.Gruppe, ta3.Zubereitung " & _
> >           "FROM ta1 " & _
> >           "INNER JOIN ta2 ON ta1.GrupID = ta2.ID2 " & _
> >           "INNER JOIN ta3 ON ta1.ZubID = ta3.ID3;"

> >  ERROR: 2147217900(80040e14)
> >    Syntax Error: missing operator in:
> >    'ta1.GrupID = ta2.ID2 INNER JOIN ta3 ON ta1.ZubID = ta3.ID3'

> > Each one of the INNER JOINs  separately works fine
> > only the combination failes.
> > Do you have another idea?

> > Regards
> > Klaus Eckert


Newsbeitrag



> > > > SQL = "Select ta1.Name, ta2.Gruppe, ta3.Zubereitung " & _
> > > >              "FROM ta1 " & _
> > > >              "INNER JOIN ta2 ON ta1.GrupID = ta2.ID2" & _
> > > >              "INNER JOIN ta3 ON ta1.ZubID = ta3.ID3; "

> > > One thing the could be wrong is the lack of a space after ta2.ID2 in
> your
> > > first join.  The resultant string would read something like:

> > > INNER JOIN ta2 ON ta1.GrupID = ta2.ID2INNER JOIN ta3 ON ta1.ZubID =
> > ta3.ID3;

> > > instead of what you are expecting...

> > > Regards,

> > > Shelby Cain

Hi Shelby,
It's great, it works fine!
I am enthused about your constructive and very quick answer.
Your solution with the implicite INNER JOINs is much more understandable
than the explicite INNER JOINs!
Please don't say once more that you aren't an SQL expert, you are!
I guess that the Access-database wanted your 'AND'
when asking impertinently for the missing operator.

Thank you very much Shelby
Regards
Klaus Eckert



Sun, 08 Dec 2002 03:00:00 GMT  
Klaus Ecker
#7 / 7
 SELECT . INNER JOIN--INNER JOIN
A short supplement:
I found that with SQL-Server7 there is no problem
to simply combine a lot of INNERJOINs.
It is not understandable for me, why even ACCESS 2000
doesn't offer the combination of INNER JOINs like the SQL-Server.
But now I know to solve the problem.
Regards
Klaus Eckert



Quote:
> Sorry -- didn't see the "using Access 2000... " statement in plain site...
> :)



> > Thank you Shelby for your hint.
> > Unfortunately the missing blanc was only in my email, not in my program.
> > The following (corrected) SQL produces the error:

> > SQL = "Select ta1.Name, ta2.Gruppe, ta3.Zubereitung " & _
> >           "FROM ta1 " & _
> >           "INNER JOIN ta2 ON ta1.GrupID = ta2.ID2 " & _
> >           "INNER JOIN ta3 ON ta1.ZubID = ta3.ID3;"

> >  ERROR: 2147217900(80040e14)
> >    Syntax Error: missing operator in:
> >    'ta1.GrupID = ta2.ID2 INNER JOIN ta3 ON ta1.ZubID = ta3.ID3'

> > Each one of the INNER JOINs  separately works fine
> > only the combination failes.
> > Do you have another idea?

> > Regards
> > Klaus Eckert


Newsbeitrag



> > > > SQL = "Select ta1.Name, ta2.Gruppe, ta3.Zubereitung " & _
> > > >              "FROM ta1 " & _
> > > >              "INNER JOIN ta2 ON ta1.GrupID = ta2.ID2" & _
> > > >              "INNER JOIN ta3 ON ta1.ZubID = ta3.ID3; "

> > > One thing the could be wrong is the lack of a space after ta2.ID2 in
> your
> > > first join.  The resultant string would read something like:

> > > INNER JOIN ta2 ON ta1.GrupID = ta2.ID2INNER JOIN ta3 ON ta1.ZubID =
> > ta3.ID3;

> > > instead of what you are expecting...

> > > Regards,

> > > Shelby Cain



Tue, 10 Dec 2002 03:00:00 GMT  
 
 Page 1 of 1
  [ 7 post ] 

 Relevant Pages 

1. PRB: Left join behaves like a inner join.

2. Problems Using SELECT and INNER JOIN

3. Inner Join Question

4. !how VB update Table A INNER JOIN Table B, Set A.co=B.ID

5. how VB update Table A INNER JOIN Table B, Set A.co=B.ID

6. how VB update Table A INNER JOIN Table B, Set A.co=B.ID

7. how VB update Table A INNER JOIN Table B, Set A.co=B.ID

8. How to execute a Inner Join between Tables on a DATASET

9. Adodc Inner Join

10. how VB update Table A INNER JOIN Table B, Set A.co=B.ID

11. INNER JOIN 2 TABLES

12. Why does this INNER JOIN not work?

  

 
Powered by phpBB® Forum Software