Hi, I created this query as part of a stored proc and the data all comes out on one line like so:
tracking info attachment info reviewer info
so each tracking info can have multiple attachments and reviewers
I was wondering if there was a way to change it so the attachments are grouped and then the reviewers are grouped.
so that way in my code I just just loop through the recordset and display it like this:
tracking info
-->all attachments
--> -->all reviewers
next tracking record etc etc
Here's the code:
ALTER PROCEDURE [dbo].[spGetAllTLInfo]
@tlid int = NULL
AS
BEGIN
SET NOCOUNT ON;
if (@tlid is null)
begin
select tl.*, c.ConID, c.ContractNum, n.*, u1.LName, u1.FName, u2.LName as "RevLN", u2.FName as "RevFN", p.ProgramName, j.ProjName, a.AID, a.attachment,
rp.*
from TrackingList tl left outer join Attachments a on tl.TL_ID = a.TL_ID
left outer join Users u1 on tl.UserID = u1.UserID
left outer join Users u2 on tl.ReviewerID = u2.UserID
left outer join ReviewersByProject rp on rp.TL_ID = tl.TL_ID and rp.ReviewerID = u2.UserID
inner join Numbers n on tl.NumID = n.NumID
inner join Contracts c on c.ConID = n.ConID
inner join Programs p on c.ProgramID = p.ProgramID
left outer join Project j on j.ProjID = c.ProjID and rp.ProjID = j.ProjID;
end
else
Begin
select tl.*, c.ConID, c.ContractNum, n.*, u1.LName, u1.FName, u2.LName as "RevLN", u2.FName as "RevFN", p.ProgramName, j.ProjName, a.AID, a.attachment,
rp.*
from TrackingList tl left outer join Attachments a on tl.TL_ID = a.TL_ID
left outer join Users u1 on tl.UserID = u1.UserID
left outer join Users u2 on tl.ReviewerID = u2.UserID
left outer join ReviewersByProject rp on rp.TL_ID = tl.TL_ID and rp.ReviewerID = u2.UserID
inner join Numbers n on tl.NumID = n.NumID
inner join Contracts c on c.ConID = n.ConID
inner join Programs p on c.ProgramID = p.ProgramID
left outer join Project j on j.ProjID = c.ProjID and rp.ProjID = j.ProjID
where tl.TL_ID = @tlid;
end
END
Best regards,
Zim
(Eternal Yak God Emperor from the Future)