SQL join, getting multiple columns with same name

Question

I have one table with a column ID and SERVICE_TYPE_TEXT, and another table with columns

ID, SERVICE_TYPE ...

and lots of other columns.

The SERVICE_TYPE in the second table contains the ID from the first table. I want to query so I can get the SERVICE_TYPE_TEXT from the first table that matches the ID given in the second table.

I've tried to join, and setting different names on ID with AS, but always at the end of the query result I get the original ID from the first table with column name ID, as well as the name I defined in the AS.

Any suggestions on how I can get the ID from the first table to stay away ? :)

Please post your select statement in the question, may be the part where you are selecting the column names — Habib
– Habib, Commented Oct 16, 2012 at 6:46
posting the actual query with sample table structure would definitly help here. — Anantha Sharma
– Anantha Sharma, Commented Oct 16, 2012 at 6:48
Maybe you should remove the * that is almost certainly somewhere in your SELECT clause (of course, this would be easier to diagnose if we could see your query) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 16, 2012 at 6:49

John Woo · Accepted Answer · 2012-10-16 06:48:01Z

21

Try something like this,

SELECT a.ID AS ServiceID,
       a.Service_Type_Text,
       b.ID AS table2ID,
       b.Service_Type
FROM   table1 a
       INNER JOIN table2 b
           ON a.ID = b.Service_Type

answered Oct 16, 2012 at 6:48

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Woo Over a year ago

what do you want for table2?

JoshySav Over a year ago

Thanks a lot! I personally didn't know you could assign columns like that.

Gidil · Accepted Answer · 2012-10-16 06:50:05Z

14

Set your query so that it returns all data from the second table but only the required field (column) from the first.
Something like this:

SELECT TAB1.SERVICE_TYPE_TEXT, TAB2.*
FROM TAB1
INNER JOIN
TAB2
ON TAB1.ID = TAB2.SERVICE_TYPE

answered Oct 16, 2012 at 6:50

Gidil

4,1472 gold badges38 silver badges50 bronze badges

Comments

simply-put · Accepted Answer · 2012-10-16 06:49:49Z

6

TRY

 SELECT a.ID AS ServiceID,
       a.Service_Type_Text,
       b.ID AS table2ID,
       b.Service_Type
FROM   table1 a
       INNER JOIN table2 b
           ON a.ID = b.Service_Type AND b.ID='YOUR_ID';

answered Oct 16, 2012 at 6:49

simply-put

1,0981 gold badge11 silver badges21 bronze badges

Collectives™ on Stack Overflow

SQL join, getting multiple columns with same name

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related