Hi There,
I have a complicated query in sql, and the query is as,
Select a.Id , m1.SenderName as FSenderName, m1.Sender as FSender,m2.sendername as LSenderName,m2.sender as LSender,m1.SentTo as FSentTo,m2.SentTo as LSentTo,m2.senton as LSentOn,a.IsRead,a.ReplyRequired,m1.Subject,a.ThreadCount from Message m1
INNER JOIN
((
Select mm.Id, min(mm.messageID) as firstMessageID, max(mm.messageID) as LastMessageID, sum(CONVERT(int, mm.isRead)) as IsRead, SUM(convert(int,mm.replyRequired)) As ReplyRequired,COUNT(mm.Id) as ThreadCount
FROM message_mailbox mm left OUTER join MessageThread mt on mm.ThreadID=mt.Id group by mm.ThreadID,mm.MailBoxId, mm.incomingoutgoing,mm .Id
having mm.MailBoxId=47 and mm.IncomingOutgoing=1
)) a
on m1.ID=a.firstMessageID
INNER Join
Message m2 on m2.ID=a.LastMessageID
Union
Select a.Id , m1.SenderName as FSenderName, m1.Sender as FSender,m2.sendername as LSenderName,m2.sender as LSender,m1.SentTo as FSentTo,m2.SentTo as LSentTo,m2.senton as LSentOn,a.IsRead,a.ReplyRequired,m1.Subject,a.ThreadCount from Message m1
INNER JOIN
((
Select mm.Id, min(mm.messageID) as firstMessageID, max(mm.messageID) as LastMessageID, sum(CONVERT(int, mm.isRead)) as IsRead, SUM(convert(int,mm.replyRequired)) As ReplyRequired,COUNT(mm.Id) as ThreadCount
FROM message_mailbox mm left outer join MessageThread mt on mm.ThreadID=mt.Id group by mm.ThreadID,mm.MailBoxId, mm.incomingoutgoing,mm.MessageId ,mm .Id
having (mm.mailboxId=19 and dbo.Isassociated(mm.MessageId,4) =1 ) and mm.IncomingOutgoing=1
)) a
on m1.ID=a.firstMessageID
INNER Join
Message m2 on m2.ID=a.LastMessageID
The Function is as,
function [dbo].[Isassociated](@messageId int, @objectId int) Returns bit
As begin
declare @isassociated int
Select @isassociated = COUNT(Id) from AssociatedMessageUsers where ObjectId=@objectId and ObjectType='Matchmaker' and MessageId=@messageId and Type=1
if @isassociated>0
return 1
return 0
end
Kindly suggest me the best way to convert in LLBl gen pro
Thanks,
Surendra Singh
