SQL server!

pts.
Tags:
Development
How do I access the field with the same field name on different tables when doing a join in SQL Server? For instant: Table A contents field status and other fields Table B also contents field status and other fields The quiery: Select A.*, B.* From A join B Where .... Then how do we access the field status on each table suppose objRec is the recordset result of the above sql if I access objRec("status") the result will always be 0 if I access objRec("A.status"), it's giving an error Please help if you know how! Thanks in advance PS: Please email me at LittleTig3r@yahoo.com

Answer Wiki

Thanks. We'll let you know when a new response is added.

First I NEVER use select *.
So all you have to do is give each common field a different alias such as:
Select a.status as Astatus, b.status as Bstatus,….

Stanton

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Randym
    Try defining each field in the select statement and give the status fields an alias: select a.field1, a.field2, a.status as A_status...., b.field1, b.field2, b.status As B_status... from A join b...
    1,740 pointsBadges:
    report
  • HP9999
    Yes, as using alias, we should not have any problem accessing the field. However, it's not my imply. The tables that I am joining contents (twenties :) hundreds of fields) and you just don't want to write the lonnnnnngggggg sql statement for all the alias just because one common field on the two tables. I think we should have a better way to access the field. Thanks
    0 pointsBadges:
    report
  • Littlew
    Why don't you design your query in enterprise manager - that way you only have to tick the boxes next to the columns rather than write an enormous SQL statement. You only have to use the alias for the columns which are the same...
    0 pointsBadges:
    report
  • DannyG
    Maybe a combination of redefining the common fields and selecting all record fields could be a solution: Select A.status as Astatus, A.*, B.status as Bstatus, B.* From A join B Where ....
    0 pointsBadges:
    report
  • Nedredgreen
    1) With a good text editor you can copy-paste all of the fields in the tables then find-replace to put in table alias names and commas (I use TextPad which is a superb text editor and creative find-replace strategy), then stick in the offending field name alias. or 2) Gather database specs. Don't take the easy way out, learn which fields are relevant for your purposes and pull the common field from only one of the tables or put an alias on that common field.
    0 pointsBadges:
    report
  • Muthu2004
    Hi, It is fine that you are trying to access all fields in both tables. If you are sure about the fields need to be referred , it is better to mention the field names in the query like Select a.field1 TF1,a.field2 TF2..,b.field1 SF1,b.Field2 SF2.. from table1 A, Table2 B where A.field=B.Field. Refer the fields like objecRec(SF1), objecRec(TF1). If there is any issue further then you need do the following tasks 1. Isssue your eralier query in the SQL Server Window 2. Get the fields with order it is displayed 3. Now execute the same query with your recordset. 4. Refer the filed like ObjRecordset.field(0).Value Where the 0 is the first field in your query. Similarly 1,2,3,,. I hope this will work for you. Regards Muthu
    0 pointsBadges:
    report
  • HP9999
    Thank all you for your suggestion. I think using the combination A.*, A.status as Astatus, B.*, B.status as Bstatus is the best solution that I expect. Once again, Thanks all you!
    0 pointsBadges:
    report
  • TATWORTH
    Try running SELECT C.Name FROM SYSCOLUMNS C, SYSOBJECTS O WHERE C.ID = O.ID AND O.NAME = 'TABLENAME' AND C.STATUS = 128 ORDER BY C.COLID This will list all the column name for table TABLENAME
    0 pointsBadges:
    report
  • JustMe2
    You need to specify the table name if front of the field you may use an alias if you like. table tblmain, tblother SELECT tblmain.one, tblother.two, tblmain.three FROM tblmain INNER JOIN tblother ON tblmain.two = tblother.two the same thing with an alias SELECT a.one, b.two, a.three FROM tblmain a INNER JOIN tblother b ON tblmain.two = tblother.two (since it is an alias you may use anything I used a and b)
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following