CODEWITHSUNDEEP
sqlsql-server
MrTex
MrTex

Reputation: 249

SQL Join two table with key into different column

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.

Upvotes: 2

Views: 82

Answers (7)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 0

Bill Tür stands with Ukraine
Bill Tür stands with Ukraine

Reputation: 3515

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

Upvotes: 1

Tharuka Madumal
Tharuka Madumal

Reputation: 201

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

Upvotes: 0

yemo
yemo

Reputation: 155

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)

Upvotes: 2

Vikram Jain
Vikram Jain

Reputation: 5588

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

Upvotes: 1

Thom A
Thom A

Reputation: 95561

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

Upvotes: 3

apomene
apomene

Reputation: 14389

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)

Upvotes: 1

Related Questions