SQL 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 2008 Forums

Transact-SQL (2008)

SQL Query help

Author

Topic

IK1972

56 Posts

Posted - 2013-10-14 : 18:12:02

drop table #TypeCode
create table #TypeCode(TypeCodeId int identity(1,1), TypeCode bigint, Name varchar(50))
insert into #TypeCode values
(3001, 'Loan Type'),
(3004, 'Transaction Type'),
(3009, 'Event Type'),
(3015, 'Participant Type')
-- select * from #TypeCode

drop table #LD
create table #LD(TDID bigint identity(1,1), TranID bigint, LoanID bigint, [Description] varchar(500))
insert into #LD values
(1234, 5467, 'sdasdas asd asdas'),
(1232, 2357, 'nbvnvb vbjjytuytu yt'),
(1344, 7989, 'yuimhjkhj'),
(1734, 2456, 'retret ghjghjghj'),
(1854, 8883, 'ewrgghjg hhgj'),
(1784, 2357, 'ytuyu ghjghjghj'),
(1284, 9734, 'werewrew ewrwerw')
-- select * from #LD

drop table #PD
create table #PD(PDID bigint, TranID bigint, [Description] varchar(500))
insert into #PD values
(3456, 1234, 'fghfg fghf hfgh'),
(2135, 1234, 'sdjhjk hjkhj khjk'),
(7564, 1854, 'wewqg ghjghj hgj')
-- select * from #PD

drop table #EventLog
create table #EventLog(EID int, SourceID bigint, SourceType bigint, Description varchar(500))
insert into #EventLog values
(1, 1234, 3004, 'sadasdadadasfsdfsd fsdfsdf'), -- 3004 means its TranID from #LD
(2, 1234, 3004, 'erewrwerwerw '),
(3, 1234, 3004, 'erewsfsdf sdfsf sdf '),
(4, 5467, 3001, 'ertre rett sdfsf sdf '), -- 3001 means its LoanID from #LD
(5, 5467, 3001, 'kljkljkl rett sdfsf sdf '),
(6, 3456, 3015, 'utyutyu tyu yutyutuy '), -- 3015 means its PDID from #PD
(7, 1784, 3004, 'sadsadasdasd asdasdsad ')

Now basically in event log for same transaction id I have different source type id data. I have one store procedure which I pass only #LD TranID and I want it return all event log for all different source type data for that transaction.
Like If I pass TranID 1234 sp should return EID 1 to 6

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-15 : 01:09:28

[code]
SELECT e.*
FROM #EventLog e
INNER JOIN (SELECT TranID AS ID
FROM #LD
WHERE TranID = @TranID
UNION ALL
SELECT PDID
FROM #PD
WHERE TranID = @TranID
UNION ALL
SELECT LoanID
FROM #LD
WHERE TranID = @TranID
)e1
ON e1.ID = e.SourceID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

IK1972

56 Posts

Posted - 2013-10-15 : 18:16:14

Thanks visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 02:15:14

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources