?
Solved

How to access field with same name from 2 tables (mysql)

Posted on 2004-09-29
15
Medium Priority
?
186 Views
Last Modified: 2012-05-05
Solution in http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21144058.html
worked for MsAccess. Please help with solution for mySql.

    sSel = "SELECT * FROM srchparam, client" _
            & " WHERE objid_client = client.objid"                           'both tables have field named "objid"
    rst.Open sSel, cnVC, adOpenKeyset, adLockOptimistic
        gSel = rst!objid                    <---- gives me objid of client, i want objid from srchparam
        gSel = rst("srchparam.objid")   <-------- works with MsAccess. But gives error in mySql
        gSel = rst("client.objid")           <------- same problem

Please help!!
0
Comment
Question by:spoowiz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +3
15 Comments
 

Author Comment

by:spoowiz
ID: 12187285
p.s. the workaround is to say:
    sSel = "SELECT * FROM client, srchparam" _            <-------- in reverse from above
            & " WHERE objid_client = client.objid"        

but i'd like to know the proper way. thanks
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 12187332
You need to explicitly select the field name, like:

select table1.A , table2.A As A2 from table1, table2 where table1.B = table2.B
0
 

Author Comment

by:spoowiz
ID: 12187344
That is not reasonably feasible as there are many fields in tables client and srchparam.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:_agj_
ID: 12187356
actually, using a select * is not good practice as also in terms of performance, since u wud be getting unnecessary records as well.

i suggest u use:

    sSel = "SELECT srchparam.objid AS objid1, client.objid AS objid2  FROM srchparam, client" _
            & " WHERE objid_client = client.objid"                          

rst("objid1")           will give srchparam.objid
rst("objid2")           will give client.objid

i am not sure what this objid_client thing in the where clause is...as u have used...but u seem to be clear on that.
0
 
LVL 7

Expert Comment

by:_agj_
ID: 12187385
try:

 "SELECT srchparam.*, client.*  FROM srchparam, client"
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 12187391
Basically you need to explicit each of them, else your statement doesn't recognize which field you refer to..

else try:

select table1.* , table2.* from table1, table2 where table1.B = table2.B

but i'm not sure will it generate error on mysql or not.. not sure..
0
 

Author Comment

by:spoowiz
ID: 12187418
agj and ryan,
i tried it. the syntax: select table1.* , table2.* from table1, table2 where table1.B = table2.B
does not give an error, however the result is the same as "select * ..."
Still same problem as described above.
0
 

Expert Comment

by:tutsamewasa
ID: 12187474
sSel = "SELECT * FROM srchparam, client" _
            & " WHERE objid_client = client.objid"                           'both tables have field named "objid"
    rst.Open sSel, cnVC, adOpenKeyset, adLockOptimistic
        gSel = rst!objid                    <---- gives me objid of client, i want objid from srchparam
        gSel = rst("srchparam.objid")   <-------- works with MsAccess. But gives error in mySql
        gSel = rst("client.objid")           <------- same problem

***********************************************************************
Well change your query to

sSel = "SELECT T1.objid as  srchparam_objid, T2.objid as client_objid FROM srchparam T1, client T2" _
            & " WHERE objid_client = T2.objid"            

    rst.Open sSel, cnVC, adOpenKeyset, adLockOptimistic

Now access it as

        gSel = rst("srchparam_objid")
        gSel = rst("client_objid")        

The as srchparam_objid and client_objid  can be any text.

Hope this helps

Good luck

Hemendra Singh Shaktawat
0
 

Author Comment

by:spoowiz
ID: 12187494
tsutsa-solution is same as what others have given. this solution is not good because I need all the othere fields as well, not just the 2 objid fields.

Please read problem again. There is a solution for MsAccess, which works. However, the same solution does not work for mySql and was hoping there would be one.
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 12187565
try select the fields explicitly and rename the fields if necessary, as mysql may got restriction on doing same tricks that can be done in Access.

You can try select several fields first for doing testing, see whether your problem can be resolved or not.

regards
0
 
LVL 39

Expert Comment

by:appari
ID: 12187734
try

"SELECT client.*  , srchparam.* FROM srchparam, client"
0
 

Expert Comment

by:tutsamewasa
ID: 12187989
Well yes I am sorry, I realised it after I hit the submit that the same solution is already given.
anyways you can do one more thing for this problem for mysql well when there are two field (or more) with the same field name in the same query the mysql apends _1, _2 etc. so for you example  there are just two fileds. You can access it as

gSel = rst("objid")           // ----- For the objid from the first table in query srchparam in your case
gSel = rst("objid_1")        // ----- For the objid from the second table in query client in your case

gSel = rst("objid_2")        // ----- If there was one more table involved


Hope this helps

Good luck

Hemendra Singh Shaktawat



0
 

Author Comment

by:spoowiz
ID: 12189936
tutsa-sounded promising. sorry to say it didn't work. rst("objid_1") or rst("objid_2") has error "not found in collection...".
thank for trying
0
 
LVL 16

Accepted Solution

by:
JohnBPrice earned 1000 total points
ID: 12189958
If you do the following, you can add a second copy of the objid columns with explicit names and still use * to get all the other columns

 sSel = "SELECT *, srchparam.objid srchparam_objid, client.objid client_objid FROM srchparam, client" _
            & " WHERE whatever"

 rst.Open sSel, cnVC, adOpenKeyset, adLockOptimistic

 gSel = rst("srchparam_objid")   <-------- will give you the srchparam objid
 gSel = rst("client_objid")           <------- will give you the client objid
0
 

Author Comment

by:spoowiz
ID: 12190077
johnprice-thank you, that works.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month11 days, 3 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question