LEFT JOIN multi column compare

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

LEFT JOIN multi column compare

Author

Topic

engcanada
Starting Member

39 Posts

Posted - 2013-06-09 : 12:22:41

I have TABLE_A with 4 int columns, TABLE_B with 1 varchar column which is compiled together from the 4 columns in TABLE_A separated with "."
I need to LEFT JOIN TABLE_A with TABLE_B ON TABLE_A.4columns = TABLE_B.1column

Thank you

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-09 : 14:54:10

[CODE]

DECLARE @T1 TABLE (i1 INT, i2 INT, i3 INT, i4 INT);
DECLARE @T2 TABLE (Val VARCHAR(20));

INSERT INTO @T1 VALUES(1111, 2, 33, 4), (5, 622, 71, 888), (2, 34, 4,5), (6, 7,8,9909), (12,11, 45, 690);
INSERT INTO @T2 Values ('1111.2.33.4'), ('5.622.71.888'), ('2.34.4.5'), ('6.7.8.9909');

SELECT T1.i4, T2.val FROM @T1 T1 LEFT JOIN
(SELECT T2.Val, CAST(SUBSTRING(T2.VAL, (LEN(T2.VAL) - CHARINDEX('.', REVERSE(VAL)) + 2),
CHARINDEX('.', REVERSE(VAL)) - 1) as INT) as LastValue from @T2 as T2) AS T2
ON T1.i4 = T2.LastValue;

[/CODE]

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-06-09 : 16:32:39

If I read op's question right, he/she wants to join all 4 columns fra table a with the combined varchar column from table_b. If this is correct, you might use this sql:


select *
  from table_a as t1
       left outer join table_b as t2
                    on cast(t1.col1 as varchar)
                      +'.'+cast(t1.col2 as varchar)
                      +'.'+cast(t1.col3 as varchar)
                      +'.'+cast(t1.col4 as varchar)
                      =t2.col1thru4

engcanada
Starting Member

39 Posts

Posted - 2013-06-09 : 20:14:33

Thank you bitsmed,
That's exactly what I was looking for. I was a little overwhelmed by MuMu88's solution and almost gave up.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-06-09 : 23:15:53

like this?

select * from @T2 a left join @T1 b on a.Val = CONCAT(b.i1, '.', b.i2, '.', b.i3, '.', b.i4)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources