CODEWITHSUNDEEP
sqlsql-server
peterbonar
peterbonar

Reputation: 599

Join On 2 Columns In 1 Row

I have a table, Table A structured as below: 

ID  oldID  newID         
1    43     97
2    54     31

And a table, Table B as below:

ID  partID 
129   43     
463   97
721   54
812   31

And a third table, Table C as below:

partID  name
  129    A
  463    B
  721    C
  812    D

And would like a query to display the following information:

oldPart  newPart
   A        B
   C        D

How would I go about getting the desired information to display?

I have the following SQL:

SELECT Table C.name
FROM Table C
WHERE (Table A.oldID = Table B.partID OR Table A.newID = Table B.partID)
AND Table B.partID = Table C.partID

And I get this:

name
 A
 B
 C
 D

Upvotes: 0

Views: 48

Answers (3)

dnoeth
dnoeth

Reputation: 60502

You need to join the tables twice:

select C1.name, C2.name
from A 
join B as B1
  on a.oldID = B1.partID 
join C AS C1
  on B1.partID = C1.ID
join B as B2
  on a.newID = B2.partID 
join C AS C2
  on B2.partID = C2.ID

Upvotes: 2

Fahmi
Fahmi

Reputation: 37483

YOu can try below using join of multiple instance of tableB and tableC

select c.name as oldpart, c1.name as newpart from
(
    SELECT b.id as  bid, b1.id as b1id
    FROM TableA a inner join tableB b on a.oldID = b.partID
    join tableB b1 on a.newID=b1.partID
)A1 join TableC c on A1.bid=c.partID
join tableC c1 on A1.b1id=c1.partid

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32001

do join

  select c.name as oldname,c1.name as newname from 
   (
        select   b.partID as oldpartID,b.ID as oldid
          b1.partID as newpartID,b1.ID as newid from
         tableA a join tabeB b  on a.oldID=b.partID
          join tableB b1 on a1.newID=b1.partID
    ) a join tableC c on a.oldid=c.partID
        join tableC c1 on a.newid=c1.partID

Upvotes: 0

Related Questions