Solved

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

Posted on 2004-09-29
15
177 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
  • 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 49

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

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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 49

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now