SELECT Bug.BugId, P.FirstName AS PrimaryAgent, temp.Closed, temp.Status, temp.Type, temp.Priority,
temp.UserCode, temp.Revision, Enterer.FirstName AS EntererAgent
FROM Bug, BugAgent, Agent, (
            SELECT DISTINCT Bug.BugId, Bug.Closed, BugStatus.Status, BugType.Type, Priority.Priority,
            Customer.UserCode, Revision.Revision
			FROM Bug, BugAgent, BugStatus, BugType, Priority, Customer
			LEFT JOIN BugRev ON BugRev.BugId = Bug.BugId
			LEFT JOIN Revision ON Revision.RevisionId = BugRev.RevisionId
			WHERE BugStatus.BugStatusId=Bug.BugStatusId
			AND Bug.BugStatusId NOT IN ( 
			    SELECT BugStatusId FROM AgentCustomBugStatus
			    WHERE AgentId= 101 AND Active=1
			)
			AND BugAgent.AgentRoleId NOT IN ( SELECT AgentRoleId from AgentViewBugRole WHERE AgentId = 101 )
			AND BugType.BugTypeId = Bug.BugTypeId
			AND Priority.PriorityId = Bug.PriorityId
			AND BugAgent.BugId = Bug.BugId AND Bug.Closed = 0
			AND BugAgent.AgentId = 101
			AND Customer.CustomerId = Bug.CustomerId
			GROUP BY Bug.BugId
) AS temp
LEFT JOIN (
            SELECT DISTINCT Bug.BugId, Agent.FirstName, BugAgent.AgentRoleId
			FROM Bug, BugAgent, BugStatus, BugType, Priority, Customer, Agent
			LEFT JOIN BugRev ON BugRev.BugId = Bug.BugId
			LEFT JOIN Revision ON Revision.RevisionId = BugRev.RevisionId
			WHERE BugStatus.BugStatusId=Bug.BugStatusId
			AND Bug.BugStatusId NOT IN (
			    SELECT BugStatusId FROM AgentCustomBugStatus
			    WHERE AgentId= 101 AND Active=1
			)
			AND BugAgent.AgentRoleId = 6 
			AND Agent.AgentId = BugAgent.AgentId
			AND BugType.BugTypeId = Bug.BugTypeId
			AND Priority.PriorityId = Bug.PriorityId
			AND BugAgent.BugId = Bug.BugId AND Bug.Closed = 0
			AND Customer.CustomerId = Bug.CustomerId
) AS Enterer ON temp.BugId = Enterer.BugId
LEFT JOIN (
            SELECT DISTINCT Bug.BugId, Agent.FirstName, BugAgent.AgentRoleId
			FROM Bug, BugAgent, BugStatus, BugType, Priority, Customer, Agent
			LEFT JOIN BugRev ON BugRev.BugId = Bug.BugId
			LEFT JOIN Revision ON Revision.RevisionId = BugRev.RevisionId
			WHERE BugStatus.BugStatusId=Bug.BugStatusId
			AND Bug.BugStatusId NOT IN (
			    SELECT BugStatusId FROM AgentCustomBugStatus
			    WHERE AgentId= 101 AND Active=1
			)
			AND BugAgent.AgentRoleId != 6 
			AND Agent.AgentId = BugAgent.AgentId
			AND BugType.BugTypeId = Bug.BugTypeId
			AND Priority.PriorityId = Bug.PriorityId
			AND BugAgent.BugId = Bug.BugId AND Bug.Closed = 0
			AND Customer.CustomerId = Bug.CustomerId
) AS P ON temp.BugId = P.BugId
WHERE Bug.BugId = temp.BugId
AND BugAgent.BugId = Bug.BugId
AND BugAgent.AgentId = Agent.AgentId
GROUP BY temp.BugId