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

[Ron]

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

[Bob Barrows [MVP]]

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]

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]" <reb01501@NOyahoo.SPAMcom> schreef in bericht
news:O$E4UY%237DHA.3012@TK2MSFTNGP09.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.
>
>

[Jeff Cochran]

On Tue, 10 Feb 2004 15:45:12 +0100, "Ron" <r@xs.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

[Bob Barrows [MVP]]

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.