SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Query help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

IK1972
Starting Member

48 Posts

Posted - 10/14/2013 :  18:12:02  Show Profile  Reply with Quote

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

India
52317 Posts

Posted - 10/15/2013 :  01:09:28  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

IK1972
Starting Member

48 Posts

Posted - 10/15/2013 :  18:16:14  Show Profile  Reply with Quote
Thanks visakh16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/16/2013 :  02:15:14  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000