Solved

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

Posted on 2004-09-29
15
181 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 51

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
Independent Software Vendors: 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 51

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 51

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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

749 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