Professional Web Applications Themes

Syntax for joing tables - ASP Database

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 ...

  1. #1

    Default Syntax for joing tables

    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.


    William E Hatto Guest

  2. #2

    Default Re: Syntax for joing tables

    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" <xxnospamxxwehbigpond.net.au> wrote in message
    news:eoRamFqYEHA.716TK2MSFTNGP11.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.
    >
    >

    Aaron [SQL Server MVP] Guest

  3. #3

    Default Re: Syntax for joing tables

    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] Guest

  4. #4

    Default Re: Syntax for joing tables

    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"


    Bob Barrows [MVP] Guest

  5. #5

    Default Re: Syntax for joing tables

    Thanks....

    The saved query worked out to be the best option.

    Cheers, Bill.


    "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> wrote in message
    news:eq1mDgqYEHA.556tk2msftngp13.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"
    >
    >

    William E Hatto Guest

Similar Threads

  1. if/else tag syntax
    By negttid in forum Macromedia Flex General Discussion
    Replies: 8
    Last Post: March 20th, 02:15 AM
  2. joing query
    By terryintransit@yahoo.com in forum MySQL
    Replies: 1
    Last Post: January 6th, 08:36 PM
  3. Joing 2 table multiple times
    By phamtum in forum Coldfusion Database Access
    Replies: 1
    Last Post: July 15th, 12:50 PM
  4. If than syntax help please
    By Nicole Johnson in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: October 12th, 06:50 PM
  5. joing xp to 2000 domain
    By Joe in forum Windows Networking
    Replies: 2
    Last Post: July 2nd, 04:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139