How do I perform a SQL JOIN on the same table twice

Question

I an new to the group and this is my first time posting a question.

I have tbl1 that contains 3 columns (id, type, priority). The data looks like this:

id | type | priority
---+------+---------
1  |   1  |  1
2  |   2  |  1
3  |   1  |  2

I have tbl2 that contains all of my references. The data looks like this:

id  | type1 | type2 | type3 | string
----+-------+-------+-------+---------
12  |   9   |   1   |    1  | single
12  |  10   |   1   |    2  | single
12  |   9   |   2   |    1  | carton
13  |   9   |   1   |    1  | low
13  |   9   |   2   |    1  | high
13  |   4   |   1   |    1  | low
14  |   9   |   1   |    1  | red
14  |   2   |   1   |    1  | red
14  |   9   |   2   |    1  | yellow

I want to join tbl2 on tbl1.type = tbl2.type2 where id=12, type1=9 and type3=1.

I also want to join tbl2 on tbl1.priority = tbl2.type2 where id=13, type1=9 and type3=1.

My resulting table should look like this:

id | type   | priority
---+--------+---------
1  | single | low
2  | carton | low
3  | single | high

Does that make any sense? In essence, there is one reference table, but it needs to be joined over and over for the various columns in table 1. I don't know if it needs to be join, left join, inner left join.

First of all, based on your question, we don't know if you need to use an inner or outer join. You are who should know what fits better with your needs. If you don't understand well the differences, I encourage you, previous to write any query, to have the concepts clear (for example, here: https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — James, May 01 '19 at 18:37
Based on the expected result, we can infer they're asking for a left join — SUMguy, May 01 '19 at 18:45
Learn what a table alias aka correlation name is & why we use one PS "I want to join" & "I also want to join" are only parts of what you want. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When describing a result: Say enough that someone could go away & come back with a solution. Please in code questions give a [mcve]--cut & paste & runnable code & desired output & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) — philipxy, May 01 '19 at 21:34
[Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) [Re relational querying.](https://stackoverflow.com/a/24425914/3404097) — philipxy, May 01 '19 at 21:41

score 1 · Answer 1 · answered May 01 '19 at 18:38

You can just join your table twice:

SELECT 
   tbl1.id, tbl2_t.string, tbl2_p.string
FROM tbl1
LEFT JOIN tbl2 AS tbl2_t ON tbl1.type = tbl2_t.type2 AND 
                               tbl2_t.id=12 AND tbl2_t.type1=9 AND tbl2_t.type3=1
LEFT JOIN tbl2 AS tbl2_p ON tbl1.priority = tbl2_p.type2 AND 
                               tbl2_p.id=13 AND tbl2_p.type1=9 AND tbl2_p.type3=1

score 0 · Answer 2 · answered May 01 '19 at 18:40

Just create a nested join using the following template: (replace text between < > with your table/column names)

SELECT * FROM (

SELECT * FROM <dbo.myTable1> a
LEFT JOIN
        (SELECT * FROM <dbo.myTable2>) b ON a.<type> = b.<type2> WHERE b.<id>=12 AND b.<type1>=9 AND b.<type3>=1

) c  
  LEFT JOIN 
         (SELECT * FROM <dbo.myTable2>) d ON c.<priority> = d.<type2> WHERE c.<id>=13 AND c.<type1>=9 AND c.<type3>=1

score 0 · Answer 3 · answered May 01 '19 at 18:41

You can use tow different table alias

select a.id,  b.string, c.string 
from tbl1 a 
inner  join tbl2 b on a.type = b.type2 
    and b.id =12 
      and b.type1=9 
        and b.type3.=1
inner join tbl2 c on a.type = c.type2 
    and c.id =13 
      and c.type1=9 
        and c.type3.=1

score 0 · Answer 4 · answered May 01 '19 at 18:51

You can use an "or" clause in the join between the two tables so that it's joining when tbl1.type tlb2.type2, but also when tbl1.priority = tbl2.type2 ... like this:

select tbl1.Id, tbl2.string 
from tbl1 inner join tbl2on (tbl1 .type  =tbl2.type1) or (tbl1 .priority = tbl2.type2)
where type1 = 9 and type3 = 1

How do I perform a SQL JOIN on the same table twice

4 Answers4