Query to join two tables using two different columns from the first table

Question

I have two tables .

Table A:

Table A ID  Table Name  owner1ID    owner2ID
1             Work1         85         91
2             Work2         86         92
3             Work3         87         93
4             Work4         88         94
5             Work5         89         95
6             Work6         90         96

Table B:

OwnerID 0WNERFIRSTNAME  0WNERlASTNAME
   85           A            M
   86           B            N
   87           C            O
   88           D            P
   90           E            Q
   91           F            R
   89           G            S
   92           H            T
   86           I            U
   94           J            V
   93           K            W
   95           L            X

Can you please help me out in getting a query where i need the table which contains TABLEID OWNERFIRSTNAME and OWNERSECONDNAME.

Expected output:

TableAID 0WNER1FIRSTNAME 0WNER1LASTNAME 0WNER2FIRSTNAME 0WNER2LASTNAME
   1            A               M              F            R

Answer 1

While MatBaile's answer is the most common practice, your own example shows some problems. First is that we lose info about table 6 for which second owner is not found in second table. This can be easily corrected with left join:

select a.id,  a.table_name, 
       b1.OwnerFirstName O1FN, b1.OwnerLastName O1LN, 
       b2.OwnerFirstName O2FN, b2.OwnerLastName O2LN
  from a 
  left join b b1 on b1.OwnerId = a.Owner1Id
  left join b b2 on b2.OwnerId = a.Owner2Id

What gives us:

        ID TABLE_NAME O1FN O1LN O2FN O2LN
---------- ---------- ---- ---- ---- ----
         1 Work1      A    M    F    R
         2 Work2      I    U    H    T               <-- two first owners
         2 Work2      B    N    H    T               <-- two first owners
         4 Work4      D    P    J    V
         3 Work3      C    O    K    W
         5 Work5      G    S    L    X
         6 Work6      E    Q                         <-- null second owner

And second problem - for table 2 we got two entries, because in your example there are two owners with id = 86. I suspect that this is typo, but this can happen in similiar cases. You can leave it as is, or take only last row (if owner changed and you have info about this in some date column), or you can list all owners using listagg(), or take max value. Things are worse when there are more rows connected to 1. and 2. owner, your output is multiplied.

As a curiosity here is unpivot-pivot solution. In this case this query looks more complicated, but if there were 10 columns you had to do 10 joins and in this query only lists of columns requires change.

select * 
  from (
    select id, table_name, type, ownerfirstname, ownerlastname
      from (select * from a unpivot (ownerId for type in (owner1ID as 1, owner2ID as 2))) a
      join b using (ownerId))
  pivot (listagg(ownerfirstname||' '||ownerlastname, ', ') within group (order by null) owner 
         for type in (1, 2))

^{SQL Fiddle demo}

        ID TABLE_NAME 1_OWNER    2_OWNER
---------- ---------- ---------- ----------
         1 Work1      A M        F R
         2 Work2      B N, I U   H T          <-- listagg() used to aggregate data
         3 Work3      C O        K W
         4 Work4      D P        J V
         5 Work5      G S        L X
         6 Work6      E Q 

Answer 2

You need to join on to TableB twice.

That means you need to give each instance of the table an alias, so you can differentiate which instance you're referring to...

SELECT
  TableA.TableAID,
  TableB1.0WNERFIRSTNAME    AS 0WNER1FIRSTNAME,
  TableB1.0WNERlASTNAME     AS 0WNER1LASTNAME,
  TableB2.0WNERFIRSTNAME    AS 0WNER2FIRSTNAME,
  TableB2.0WNERlASTNAME     AS 0WNER2LASTNAME
FROM
  TableA
INNER JOIN
  TableB    TableB1
    ON TableB1.OwnerID = TableA.owner1ID
INNER JOIN
  TableB    TableB2
    ON TableB2.OwnerID = TableA.owner2ID

P.S. Don't Spell 0WNERFIRSTNAME with a ZERO, Spell it OWNERFIRSTNAME!