|
 |
asp_databases thread: Joining three tables.
Message #1 by "T.L. Jackson" <actionjackson@a...> on Wed, 27 Dec 2000 09:38:57 -0000
|
|
How would one go joining three tables using inner joins? I have two of
the three tables joined but I'm having problems with the third table. I
keep getting an error that states wrong syntax in the from clause and then
when I change it, I get another error that states wrong syntax in the
where clause and then once I fix that error it starts over again. The
following is the sql statement I'm using for two joined tables: (this one
works for two tables)
--------------------------------------
The SQL Statement:
--------------------------------------
objConn1.Open "Select * from tblMembers INNER JOIN tblBusiness ON
tblBusiness.PK_fk_UserID = tblMembers.PK_UserID Where LName LIKE '" &
LName & "' OR FName LIKE '" & FName & "';",objConn
--------------------------------------
I have shorten down the sql statement for this post. I'm not using the
"*" in the select statement. I just didn't feel like writing all the
things out again. The following is information about the third table.
--------------------------------------
Information on Third Table:
--------------------------------------
Table Name: tblDegrees
Primary Key: PK_fk_UserID
This table is being joined to the tblMembers table which it's primary key
is PK_UserId
--------------------------------------
T.L.
---
FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS
IN YOUR INBOX!
Get the latest and best HTML, XML, and JavaScript tips, tools, and
developments from the experts. Sign up for one or more of EarthWeb's
FREE IT newsletters at http://www.earthweb.com today!
---
You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com
Message #2 by "Pappas Nikos" <pappas@c...> on Wed, 27 Dec 2000 15:49:02 -0800
|
|
Hmm all I do is use access to do that for me creating a query and then view
SQL source for that.
Maybe you'll have to modify it a little bit like deleting the ; in he end
and the way you refer to fieldnames like brackets [] .
I thing you will find it if you experiment with this a little.
I hope this will help a bit that's what I do with VB6 not too sure about asp
Regards
Nkos
-----Original Message-----
From: T.L. Jackson [mailto:actionjackson@a...]
Sent: Wednesday, December 27, 2000 1:39 AM
To: ASP Databases
Subject: [asp_databases] Joining three tables.
How would one go joining three tables using inner joins? I have two of
the three tables joined but I'm having problems with the third table. I
keep getting an error that states wrong syntax in the from clause and then
when I change it, I get another error that states wrong syntax in the
where clause and then once I fix that error it starts over again. The
following is the sql statement I'm using for two joined tables: (this one
works for two tables)
--------------------------------------
The SQL Statement:
--------------------------------------
objConn1.Open "Select * from tblMembers INNER JOIN tblBusiness ON
tblBusiness.PK_fk_UserID = tblMembers.PK_UserID Where LName LIKE '" &
LName & "' OR FName LIKE '" & FName & "';",objConn
--------------------------------------
I have shorten down the sql statement for this post. I'm not using the
"*" in the select statement. I just didn't feel like writing all the
things out again. The following is information about the third table.
--------------------------------------
Information on Third Table:
--------------------------------------
Table Name: tblDegrees
Primary Key: PK_fk_UserID
This table is being joined to the tblMembers table which it's primary key
is PK_UserId
--------------------------------------
T.L.
---
FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS
IN YOUR INBOX!
Get the latest and best HTML, XML, and JavaScript tips, tools, and
developments from the experts. Sign up for one or more of EarthWeb's
FREE IT newsletters at http://www.earthweb.com today!
---
You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com
Message #3 by "Steven Lee" <steventlee@z...> on Wed, 27 Dec 2000 11:01:57 -0500
|
|
Try this
select m.*
from tblMembers m, tblBusiness b, tblDegrees d
where d.PK_fk_UserID = m.PK_UserId
and b.PK_fk_UserID = m.PK_UserId
and m.LName LIKE '" & mLName & "' OR m.FName LIKE '" & m.FName & "'"
---- "T.L. Jackson" <actionjackson@a...> wrote:
> How would one go joining three tables using inner joins? I have two
> of
> the three tables joined but I'm having problems with the third table.
> I
> keep getting an error that states wrong syntax in the from clause and
> then
> when I change it, I get another error that states wrong syntax in the
> where clause and then once I fix that error it starts over again. The
> following is the sql statement I'm using for two joined tables: (this
> one
> works for two tables)
>
> --------------------------------------
> The SQL Statement:
> --------------------------------------
>
> objConn1.Open "Select * from tblMembers INNER JOIN tblBusiness ON
> tblBusiness.PK_fk_UserID = tblMembers.PK_UserID Where LName LIKE '"
> &
> LName & "' OR FName LIKE '" & FName & "';",objConn
>
> --------------------------------------
>
> I have shorten down the sql statement for this post. I'm not using
> the
> "*" in the select statement. I just didn't feel like writing all the
> things out again. The following is information about the third table.
>
> --------------------------------------
> Information on Third Table:
> --------------------------------------
>
> Table Name: tblDegrees
> Primary Key: PK_fk_UserID
>
> This table is being joined to the tblMembers table which it's primary
> key
> is PK_UserId
>
> --------------------------------------
>
> T.L.
>
---
FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND
INSIGHTS IN YOUR INBOX!
Get the latest and best C++, Visual C++, Java, Visual Basic, and XML tips, tools, and
developments from the experts. Sign up for one or more of EarthWeb?s
FREE IT newsletters at http://www.earthweb.com today!
---
You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com
Message #4 by "Wally Burfine" <oopconsultant@h...> on Wed, 27 Dec 2000 21:41:24 -0000
|
|
You need something like this:
FROM (tblA INNER JOIN tblB ON tblA.FldA1 = tblB.fldB1) INNER JOIN tblC ON
tblB.fldB2 = tblC.fldC1
Good Luck
Wally Burfine
>From: "T.L. Jackson" <actionjackson@a...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] Joining three tables.
>Date: Wed, 27 Dec 2000 09:38:57 -0000
>
>How would one go joining three tables using inner joins? I have two of
>the three tables joined but I'm having problems with the third table. I
>keep getting an error that states wrong syntax in the from clause and then
>when I change it, I get another error that states wrong syntax in the
>where clause and then once I fix that error it starts over again. The
>following is the sql statement I'm using for two joined tables: (this one
>works for two tables)
>
>--------------------------------------
>The SQL Statement:
>--------------------------------------
>
>objConn1.Open "Select * from tblMembers INNER JOIN tblBusiness ON
>tblBusiness.PK_fk_UserID = tblMembers.PK_UserID Where LName LIKE '" &
>LName & "' OR FName LIKE '" & FName & "';",objConn
>
>--------------------------------------
>
>I have shorten down the sql statement for this post. I'm not using the
>"*" in the select statement. I just didn't feel like writing all the
>things out again. The following is information about the third table.
>
>--------------------------------------
>Information on Third Table:
>--------------------------------------
>
>Table Name: tblDegrees
>Primary Key: PK_fk_UserID
>
>This table is being joined to the tblMembers table which it's primary key
>is PK_UserId
>
>--------------------------------------
>
>T.L.
>
---
NEED TECHNICAL TIPS, TOOLS, AND INSIGHTS? Is FREE okay?
Visit EarthWeb for the latest in IT Management, Software Development,
Web Development, Networking & Communications, and Hardware & Systems.
Click on http://www.earthweb.com for FREE articles, tutorials,
and discussions from the experts.
---
You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com
|
|
|
View Cart
