SQL Join two table with key into different column

user_10463522 0
Viewed 60

I have two table, in SQL; the first table(T1) is this:

ID  
----
401 
402
403
634
635
636

The second table(T2) is this:

ID1 | VALUE1 | ID2 | VALUE2
---- -------- ----- -------
401 | TEST   | 634 | SAMPLE1
402 | MYVAL  | 635 | TRUE

The result i need is this:

T1.ID | T2.ID1| T2.VALUE1 | T2.ID2 | T2.VALUE2
------ ------- ----------- -------- ----------
401   | 401   | TEST      | 634    | SAMPLE1
402   | 402   | MYVAL     | 635    | TRUE
634   | 401   | TEST      | 634    | SAMPLE1
635   | 402   | MYVAL     | 635    | TRUE

The value 403 and 636 of T1 must not be present in the result because don't have any reference in T2.

There is a way to accomplish this with some INNER JOIN? I'm using MS SQL Server 2017.

sql-server sql
7 Answers

Wouldn't this just be in IN clause?

SELECT *
FROM dbo.Table1 T1
     JOIN dbo.Table2 T2 ON T1.ID IN (T2.ID1,T2.ID2)

DB<>Fiddle

user_20299830

Suppose you can do simple join tables like this:

select t1.id, t2.id1, t2.value1, t2.id2, t2.value2
from t1
join t2 on (t1.id = t2.id1 or t1.id = t2.id2)
user_97098280

You need INNER JOIN AND UNION. Try like

(SELECT T1.ID , T2.ID1, T2.VALUE1 , T2.ID2 , T2.VALUE2 FROM
  T1 INNER JOIN T2 ON t1.ID = T2.ID1)
UNION 
(SELECT T1.ID , T2.ID1, T2.VALUE1 , T2.ID2 , T2.VALUE2 FROM
  T1 INNER JOIN T2 ON t1.ID = T2.ID2)
user_10008270

You could use OR in your JOIN condition:

SELECT T1.ID, T2.ID1, T2.VALUE1, T2.ID2, T2.VALUE2
    FROM T1
    INNER JOIN T2
        ON T1.ID = T2.ID1 OR T1.ID = T2.ID2

Demo

user_39592590

Please, try with belwo query:

SELECT T1.ID , T2.ID1, T2.VALUE1 , T2.ID2 , T2.VALUE2
FROM dbo.Table1 T1
LEFT OUTER JOIN dbo.Table1 T2 ON T1.ID IN (T2.ID1,T2.ID2)
WHERE T2.ID1 IS NOT NULL
user_11228790

Is this query output what you need? 

SELECT t1.ID, t2.ID1, t2.VALUE1, t2.ID2, t2.VALUE2
FROM T1 t1
INNER JOIN T2 t2 ON t1.ID = t2.ID1

UNION

SELECT t1.ID, t2.ID1, t2.VALUE1, t2.ID2, t2.VALUE2
FROM T1 t1
INNER JOIN T2 t2 ON t1.ID = t2.ID2
user_129101570

You seem to want:

select v.id, t2.*
from table2 t2 cross apply
     (values (t2.id1), (t2.id2)) as v(id);

I don't see what table1 has to do with the result set. Hence, join does not seem very useful.

user_11440350
Related