Syntax for joing tables

[William E Hatto]

Hi all,

I am using asp with an access 2002 database. I have 3 tables.

tblAssets tblDescriptions tblEmployees

AssetID DescriptionID EmployeeID
DescriptionID Description FirstName
EmployeeID Type LastName
SerialNumber
DatePurchased


When retreiving data from tblAssets I want to display the data assoctiated
with each key, ie EmployeeID and DescriptionID.

I am using a JOIN statement to get the Description but cannot work out how
to do it for the employee table.
THis is myy current code.

strSQL="SELECT * FROM tblAssets INNER JOIN tblAssetList ON
tblAssets.ModelID=tblAssetList.ModelID"
strSQL=strSQL & " ORDER BY Description, Type, DatePurchased"

What is the syntax for using multiple JOIN statements in a SQL string.


Thanks, Bill.

[Aaron [SQL Server MVP]]

Access has some quirks, I believe, with multiple join statements (you may
need to add parentheses somewhere). But basically, it's like this:

SELECT <column_list>
FROM table1
INNER JOIN table2
ON table1.key = table2.key
INNER JOIN table3
ON table2.key = table3.key

Don't use SELECT *, especially when joining multiple tables (where you
guaranteed to return redundant data!).

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)





"William E Hatto" <xxnospamxxweh@bigpond.net.au> wrote in message
news:eoRamFqYEHA.716@TK2MSFTNGP11.phx.gbl...

> Hi all,
>
> I am using asp with an access 2002 database. I have 3 tables.
>
> tblAssets tblDescriptions tblEmployees
>
> AssetID DescriptionID EmployeeID
> DescriptionID Description FirstName
> EmployeeID Type LastName
> SerialNumber
> DatePurchased
>
>
> When retreiving data from tblAssets I want to display the data assoctiated
> with each key, ie EmployeeID and DescriptionID.
>
> I am using a JOIN statement to get the Description but cannot work out how
> to do it for the employee table.
> THis is myy current code.
>
> strSQL="SELECT * FROM tblAssets INNER JOIN tblAssetList ON
> tblAssets.ModelID=tblAssetList.ModelID"
> strSQL=strSQL & " ORDER BY Description, Type, DatePurchased"
>
> What is the syntax for using multiple JOIN statements in a SQL string.
>
>
> Thanks, Bill.
>
>

[Bob Barrows [MVP]]

William E Hatto wrote:

> Hi all,
>
> I am using asp with an access 2002 database. I have 3 tables.
>
> tblAssets tblDescriptions tblEmployees
>
> AssetID DescriptionID EmployeeID
> DescriptionID Description FirstName
> EmployeeID Type LastName
> SerialNumber
> DatePurchased
>
>
> When retreiving data from tblAssets I want to display the data
> assoctiated with each key, ie EmployeeID and DescriptionID.
>
> I am using a JOIN statement to get the Description but cannot work
> out how to do it for the employee table.
> THis is myy current code.
>
> strSQL="SELECT * FROM tblAssets INNER JOIN tblAssetList ON

Don't use selstar (Select *) - especially with multitable queries! Specify
the fields you want to return so that you don't return several columns
containing the same data. [url]http://www.aspfaq.com/show.asp?id=2096[/url]

> tblAssets.ModelID=tblAssetList.ModelID"
> strSQL=strSQL & " ORDER BY Description, Type, DatePurchased"

??? I don't see tblAssetList or ModelID in the above schema description ...

>
> What is the syntax for using multiple JOIN statements in a SQL string.
>
>
> Thanks, Bill.

Jet is very fussy about requiring multiple joins to be nested using
parentheses. I could never remember where Jet wanted the parentheses to be
placed, so I always used the Access Query Builder to build my multi-join SQL
statements. I think this is how it should look, but you should test it using
the Access Query Builder (frankly, ALL queries should be built and tested
using the tools provided for the particular database you are using before
attempting to run them from ASP or any other client application):

strSQL="SELECT a.SerialNumber, a.DatePurchased, " & _
"d.Description, d.Type, e.FirstName, e.LastName " & _
FROM (tblAssets a INNER JOIN " & _
"tblDescriptions d ON a.DescriptionID = d.DescriptionID) " & _
"INNER JOIN tblEmployees e ON a.tblEmployeeID = " & _
"e.tblEmployeeID " & _
" ORDER BY d.Description, d.Type, a.DatePurchased"

'for debugging
Response.Write strSQL


An alternative, if you are planning to use one of the joins frequently, is
to create a View (a saved query) that performs the join:

Select a.SerialNumber, a.DatePurchased, d.Description, d.Type
FROM tblAssets a INNER JOIN tblDescriptions d
ON a.DescriptionID = d.DescriptionID

Save the query as qAssetDescriptions. Now you can use the View as you would
use a table in a new query:

Select a.SerialNumber, a.DatePurchased, a.Description, a.Type
e.FirstName, e.LastName
FROM qAssetDescriptions a INNER JOIN Employees e
ON a.EmployeeID = e.EmployeeID

HTH,
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"

[Bob Barrows [MVP]]

Bob Barrows [MVP] wrote:

> An alternative, if you are planning to use one of the joins
> frequently, is to create a View (a saved query) that performs the
> join:
>
> Select a.SerialNumber, a.DatePurchased, d.Description, d.Type
> FROM tblAssets a INNER JOIN tblDescriptions d
> ON a.DescriptionID = d.DescriptionID
>

Correction:

Select a.SerialNumber, a.DatePurchased, a.EmployeeID,
d.Description, d.Type
FROM tblAssets a INNER JOIN tblDescriptions d
ON a.DescriptionID = d.DescriptionID

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"

[William E Hatto]

Thanks....

The saved query worked out to be the best option.

Cheers, Bill.


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eq1mDgqYEHA.556@tk2msftngp13.phx.gbl...

> Bob Barrows [MVP] wrote:

> > An alternative, if you are planning to use one of the joins
> > frequently, is to create a View (a saved query) that performs the
> > join:
> >
> > Select a.SerialNumber, a.DatePurchased, d.Description, d.Type
> > FROM tblAssets a INNER JOIN tblDescriptions d
> > ON a.DescriptionID = d.DescriptionID
> >

> Correction:
>
> Select a.SerialNumber, a.DatePurchased, a.EmployeeID,
> d.Description, d.Type
> FROM tblAssets a INNER JOIN tblDescriptions d
> ON a.DescriptionID = d.DescriptionID
>
> 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"
>
>