CODEWITHSUNDEEP
sqljoin
Aune
Aune

Reputation: 253

SQL join, getting multiple columns with same name

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 ? :)

Upvotes: 18

Views: 102494

Answers (3)

Gidil
Gidil

Reputation: 4137

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

Upvotes: 14

simply-put
simply-put

Reputation: 1088

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';

Upvotes: 6

John Woo
John Woo

Reputation: 263723

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

Upvotes: 21

Related Questions