TSQL Query Help - SQL Server Forums

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

SQL Server 2012 Forums

Transact-SQL (2012)

TSQL Query Help

Author

Topic

IK1972

56 Posts

Posted - 2014-10-29 : 18:27:26

If object_id('tempdb..#LD') is not null drop table #LD
create table #LD(ID int identity, LA_Type_ID int, LA_Value int, LID bigint unique, MText varchar(255))

insert into #LD values
(22901, 1652, 1231, 'ABC'),
(22901, 1652, 1232, 'ABC'),
(22901, 1653, 1233, 'ABC'),
(22901, 1654, 1234, 'ABC'),
(22901, 1655, 1235, 'ABC'),
(22902, 64, 1236, 'ABC'),
(22902, 67, 1237, 'ABC'),
(22902, 68, 1238, 'ABC'),
(22902, 64, 1239, 'ABC')
-- select * from #LD

If object_id('tempdb..#MD') is not null drop table #MD
create table #MD(UID int, BID varchar(255), BWG varchar(255))
insert into #MD values (567, '1652,1654', '64,67')
-- select * from #MD

-- Expected Result:
-- For every row in #LD table we need to check the #MD table. When LA_Type_ID = 22901 then we need to check #MD table BID column and when its 22902 then BWG column.

-- (22901, 1652, 1231, 'ABC'), -- Include this row in result set as LA_Type_ID 22901 and LA_Value is 1652 and its exists in #MD Table BID column
-- (22901, 1652, 1232, 'ABC'), -- Include this row in result set as LA_Type_ID 22901 and LA_Value is 1652 and its exists in #MD Table BID column
-- (22901, 1653, 1233, 'ABC'), -- Exclude this row as LA_Vlaue 1653 is not match in #MD table BID column
-- (22901, 1654, 1234, 'ABC'), -- Include this row in result set as LA_Type_ID 22901 and LA_Value is 1654 and its exists in #MD Table BID column
-- (22901, 1655, 1235, 'ABC'), -- Exclude this row as LA_Vlaue 1655 is not match in #MD table BID column
-- (22902, 64, 1236, 'ABC'), -- Include this row in result set as LA_Type_ID 22902 and LA_Value is 64 and its exists in #MD Table BWG column
-- (22902, 67, 1237, 'ABC'), -- Include this row in result set as LA_Type_ID 22902 and LA_Value is 67 and its exists in #MD Table BWG column
-- (22902, 68, 1238, 'ABC'), -- Exclude this row as LA_Vlaue 68 is not match in #MD table BWG column
-- (22902, 64, 1239, 'ABC') -- Include this row in result set as LA_Type_ID 22902 and LA_Value is 64 and its exists in #MD Table BWG column

-- Please remember I have big data set in #LD table

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-30 : 09:19:51

[code]
select (LA_Type, LA_Value, LID, MText)
from #LD ld
join #MD md
on ld.LA_TYPE = 22901 and md.BID like '%' + ld.LA_Value' + '%'
or ld.LA_Type = 22902 and md.BWG like '%' + ld.LA_Value' + '%'
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources