CODEWITHSUNDEEP
sql-serverjoin
xyz
xyz

Reputation: 782

join by column type

I have 2 tables like the following.

id1 | val | type
1     2     type1
1     4     type2
2     9     type2
2     7     type1

id2|type1|type2
11  2      4
33  7      9

 I need result like this
id1|id2
1   11
2    33

I need to check both type1 and type2 to relate id1 and id2. I tried the following query but it does not work.

 select id1,id2 from t1 inner join t2 on (type='type1' and                             
 t1.val=t2.type1)and (type='type2' and t1.val=t2.type2)

Upvotes: 1

Views: 42

Answers (2)

John
John

Reputation: 384

I believe this should give you what you want:

SELECT
    a.id1,
    t2.id2
FROM
    t2
INNER JOIN
    t1 a ON a.val = t2.type1 AND a.type = 'type1'
INNER JOIN
    t1 b ON b.val = t2.type2 AND b.type = 'type2'
WHERE
    a.id1 = b.id1

Upvotes: 1

M.Ali
M.Ali

Reputation: 69524

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE t1(id1 INT,  val INT,  [type] VARCHAR(10))
INSERT INTO t1 VALUES
(1 ,    2     ,'type1'),
(1 ,    4     ,'type2'),
(2 ,    9     ,'type2'),
(2 ,    7     ,'type1')


CREATE  TABLE t2(id2 INT,  [type1] VARCHAR(10),  [type2] VARCHAR(10))
INSERT INTO t2 VALUES
(11 , 2  ,    4),
(33 , 7  ,    9)

Query 1:

Select t1.id1 , t2.id2 
from t1
inner join 
(Select * from t2 t 
      UNPIVOT (Val for [type]  IN ([type1],[type2]))up) t2
ON t1.val = t2.Val and t1.type = t2.type
GROUP BY t1.id1 , t2.id2

Results:

| id1 | id2 |
|-----|-----|
|   1 |  11 |
|   2 |  33 |

Upvotes: 1

Related Questions