Professional Web Applications Themes

ASP / Access: use of same table twice in one query - ASP Database

Hi, In a query I use the same table twice: Select u.*, ud.*, dep.*, u2.* from users as u, departments as dep, user_in_departments as ud, users as u2 where u.user_id = ud.ud_user_id and dep.dep_code=ud.ud_dep_code and u.user_manager=u2.user_id and u.user_id = john1 I want to retrieve the data from the user and his manager, which are stored in the same table. When I try to retrieve this data, I am allowed to use fieldnames, but I ca not include the tablename: user = objRs("User_Name") is ok usermanager = objRs("U2.User_id") is not ok, I can not use u2 to define the tablename. The ...

  1. #1

    Default ASP / Access: use of same table twice in one query

    Hi,

    In a query I use the same table twice:

    Select u.*, ud.*, dep.*, u2.*
    from users as u, departments as dep, user_in_departments as ud, users as u2
    where u.user_id = ud.ud_user_id
    and dep.dep_code=ud.ud_dep_code
    and u.user_manager=u2.user_id
    and u.user_id = john1

    I want to retrieve the data from the user and his manager, which are stored
    in the same table.

    When I try to retrieve this data, I am allowed to use fieldnames, but I ca
    not include the tablename:

    user = objRs("User_Name") is ok
    usermanager = objRs("U2.User_id") is not ok, I can not use u2 to
    define the tablename.

    The data is stored in an Access database. Is this not possible in Access in
    combination with ASP? Do I really have to specify the individual fieldnames
    and name them in the select statement, i.e. Select u.user_id as userid,
    u2.user_id as manageruserid?

    Thx,
    Ron



    Ron Guest

  2. #2

    Default Re: ASP / Access: use of same table twice in one query

    That's right. You cannot use the table names. They are not part of the field
    names supplied to ado. The solution is to use the good programming practice
    of always explicitly naming the fields you want your query to return,
    avoiding the use of selstar (select *).

    Your query is returning too many columns. You have two user_id columns that
    both contain the same data, etc. Why do you need to return both? It's a
    waste of processing time and network traffic. By explicitly naming the
    columns you wish to return, you avoid returning duplicate data, and you
    improve performance by not requiring ado to make an extra trip to the
    database to get the field names.

    Oh! I see that you answered your own question below. Yes, you need to " ...
    specify the individual fieldnames and name them in the select statement,
    i.e. Select u.user_id as userid, u2.user_id as manageruserid."

    HTH,
    Bob Barrows

    Ron wrote:
    > Hi,
    >
    > In a query I use the same table twice:
    >
    > Select u.*, ud.*, dep.*, u2.*
    <snip>
    >
    > When I try to retrieve this data, I am allowed to use fieldnames, but
    > I ca not include the tablename:
    >
    > user = objRs("User_Name") is ok
    > usermanager = objRs("U2.User_id") is not ok, I can not use u2 to
    > define the tablename.
    >
    > The data is stored in an Access database. Is this not possible in
    > Access in combination with ASP? Do I really have to specify the
    > individual fieldnames and name them in the select statement, i.e.
    > Select u.user_id as userid, u2.user_id as manageruserid?
    >
    > Thx,
    > Ron
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Barrows [MVP] Guest

  3. #3

    Default Re: ASP / Access: use of same table twice in one query

    Thx,
    I needed the two user tables, because the data of the user and his manager
    are in the same table and I need them both in one query.
    I agree it is better to specify the fields I need in the select statement,
    but as I needed it all...

    Thanks for your fast reponse,
    Kind regards,
    Ron


    "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> schreef in bericht
    news:O$E4UY%237DHA.3012TK2MSFTNGP09.phx.gbl...
    > That's right. You cannot use the table names. They are not part of the
    field
    > names supplied to ado. The solution is to use the good programming
    practice
    > of always explicitly naming the fields you want your query to return,
    > avoiding the use of selstar (select *).
    >
    > Your query is returning too many columns. You have two user_id columns
    that
    > both contain the same data, etc. Why do you need to return both? It's a
    > waste of processing time and network traffic. By explicitly naming the
    > columns you wish to return, you avoid returning duplicate data, and you
    > improve performance by not requiring ado to make an extra trip to the
    > database to get the field names.
    >
    > Oh! I see that you answered your own question below. Yes, you need to "
    ....
    > specify the individual fieldnames and name them in the select statement,
    > i.e. Select u.user_id as userid, u2.user_id as manageruserid."
    >
    > HTH,
    > Bob Barrows
    >
    > Ron wrote:
    > > Hi,
    > >
    > > In a query I use the same table twice:
    > >
    > > Select u.*, ud.*, dep.*, u2.*
    > <snip>
    > >
    > > When I try to retrieve this data, I am allowed to use fieldnames, but
    > > I ca not include the tablename:
    > >
    > > user = objRs("User_Name") is ok
    > > usermanager = objRs("U2.User_id") is not ok, I can not use u2 to
    > > define the tablename.
    > >
    > > The data is stored in an Access database. Is this not possible in
    > > Access in combination with ASP? Do I really have to specify the
    > > individual fieldnames and name them in the select statement, i.e.
    > > Select u.user_id as userid, u2.user_id as manageruserid?
    > >
    > > Thx,
    > > Ron
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >

    Ron Guest

  4. #4

    Default Re: ASP / Access: use of same table twice in one query

    On Tue, 10 Feb 2004 15:45:12 +0100, "Ron" <rxs.nl> wrote:
    >Hi,
    >
    >In a query I use the same table twice:
    >
    >Select u.*, ud.*, dep.*, u2.*
    > from users as u, departments as dep, user_in_departments as ud, users as u2
    >where u.user_id = ud.ud_user_id
    >and dep.dep_code=ud.ud_dep_code
    >and u.user_manager=u2.user_id
    >and u.user_id = john1
    >
    >I want to retrieve the data from the user and his manager, which are stored
    >in the same table.
    >
    >When I try to retrieve this data, I am allowed to use fieldnames, but I ca
    >not include the tablename:
    >
    >user = objRs("User_Name") is ok
    >usermanager = objRs("U2.User_id") is not ok, I can not use u2 to
    >define the tablename.
    >
    >The data is stored in an Access database. Is this not possible in Access in
    >combination with ASP? Do I really have to specify the individual fieldnames
    >and name them in the select statement, i.e. Select u.user_id as userid,
    >u2.user_id as manageruserid?
    You really should be doing this anyway, as a good programming
    practice. And yes, for this you need to specify the field names.
    Though I suppose you could drop your result set into an array and use
    that, but that's double work in programming and far worse than simply
    using a proper query in the first place.

    Jeff
    Jeff Cochran Guest

  5. #5

    Default Re: ASP / Access: use of same table twice in one query

    Ron wrote:
    > Thx,
    > I needed the two user tables,
    That's not what I said. I said your query was returning two user_id fields,
    each containing the same data. Look: "u.user_id = ud.ud_user_id". These two
    fields contain the same data, yet, due to your use of selstar, you're
    returning both when only one is needed.
    >because the data of the user and his
    > manager are in the same table and I need them both in one query..
    I understand that: I was not questioning that
    > I agree it is better to specify the fields I need in the select
    > statement, but as I needed it all...
    Don't get lazy ;-) You did not need them all ...

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Barrows [MVP] Guest

Similar Threads

  1. Copy a table in Access structure only with CF query
    By jimWPX in forum Coldfusion Database Access
    Replies: 3
    Last Post: June 4th, 02:33 PM
  2. 2 table query problem
    By glossen in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 24th, 12:01 PM
  3. Replies: 1
    Last Post: August 10th, 12:41 PM
  4. Mulitple Table Query Help
    By Ralph Freshour in forum PHP Development
    Replies: 1
    Last Post: September 3rd, 03:49 AM
  5. Temporary Table Query
    By Jonathan Derbyshire in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 4th, 07:44 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