Help Needed in Join

1

Hi,

Below are my sample data to play with

declare @Messages table(MessageId int identity(1,1),MessageName varchar(50),Description varchar(4000),startDate date,EndDate Date)
insert into @Messages(MessageName,Description,startDate,EndDate)
select 'Sample1','Sample DEsc',CAST(GETDATE() as date),CAST(GETDATE()+10 as date) union all
select 'Sample2','Sample DEsc2',CAST(GETDATE() as date),CAST(GETDATE()+11 as date) union all
select 'Sample3','Sample DEsc3',CAST(GETDATE() as date),CAST(GETDATE()+12 as date) union all
select 'Sample4','Sample DEsc4',CAST(GETDATE() as date),CAST(GETDATE()+13 as date) 

declare @Vendors_Messages table(VendorId int,MessageId int);

 insert into @Vendors_Messages
 select 100,1 union all 
 select 200,1 union all
 select 200, 2

declare @Company_Messages table(MessageId int,VendorId int,companyID int);

 insert into @Company_Messages(MessageId,VendorId,companyID)
 select 3,100,1000 union all 
 select 4,200, 1001

I would be passing VendorID,CompanyID always and i need to get the messages falls under the current date.

for example if i pass vendorid = 100 and companyid = 1000 i need to get displayed the below data order by latest message

select 'Sample1','Sample DEsc' union all select 'Sample3','Sample DEsc3'

Any better way how to achieve this and any sample query please

3

SELECT
M.*
FROM @Messages M
LEFT JOIN @Vendors_Messages VM
ON M.MessageId = VM.MessageId
LEFT JOIN @Company_Messages Cm
ON Cm.MessageId = M.MessageId
AND Cm.companyID = 1000
WHERE ISNULL(VM.VendorId, Cm.VendorId) = 100
ORDER BY M.startDate DESC

4

thank you Gentle man for the reply.