• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

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

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
spoowiz
Asked:
spoowiz
  • 6
  • 3
  • 2
  • +3
1 Solution
 
spoowizAuthor Commented:
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
 
Ryan ChongCommented:
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
 
spoowizAuthor Commented:
That is not reasonably feasible as there are many fields in tables client and srchparam.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
_agj_Commented:
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
 
_agj_Commented:
try:

 "SELECT srchparam.*, client.*  FROM srchparam, client"
0
 
Ryan ChongCommented:
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
 
spoowizAuthor Commented:
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
 
tutsamewasaCommented:
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
 
spoowizAuthor Commented:
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
 
Ryan ChongCommented:
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
 
appariCommented:
try

"SELECT client.*  , srchparam.* FROM srchparam, client"
0
 
tutsamewasaCommented:
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
 
spoowizAuthor Commented:
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
 
JohnBPriceCommented:
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
 
spoowizAuthor Commented:
johnprice-thank you, that works.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now