Click here to Skip to main content
15,897,187 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,I want to know if this is possible..
SQL
SELECT  TM.ticketnumber
	,TM.status 				  
	,TM.priority 
	,TD.createddate
	,TD.subject
	,TD.message
	,PU.Name
	,TM.createduserid AS createduserid
	,TC.name AS CategoryName
	,TC.id AS CategoryID
	,PU.Email AS CreatedUserEmail
	,PU1.Email AS AssignedUserEmail
	,TM.assigneduserid AS AssignedTo
	,TA.filename AS Attachment
	,TD.isprivatenote AS PrivateNote
	,TS.emailaddress AS managerMail
	
FROM  ticketmaster TM 
INNER JOIN ticketdetail TD ON TM.id=TD.ticketmasterid 
AND TD.createddate=(SELECT MIN(ticketdetail.createddate) 
FROM ticketdetail  WHERE ticketdetail.ticketmasterid=TM.id)
LEFT JOIN PortalUser PU ON PU.ID=TM.createduserid AND PU.Status=1
LEFT JOIN PortalUser PU1 ON PU1.ID=TM.assigneduserid AND PU1.Status=1
INNER JOIN ticketcategory TC ON TC.id=TM.categoryid AND TC.isactive=1 
LEFT JOIN ticketattachment TA ON TA.ticketdetailid=TD.id
INNER JOIN ticketsettings TS ON TS.DistributorID=@CompanyID OR TS.ResellerID=@CompanyID 
OR TS.BusinessID=@CompanyID OR TS.BusinessSiteID=@CompanyID
WHERE TM.id=@TicketID


SET @assigneduserid=(the assigneduserid from above select)
Posted
Comments
Arjun Menon U.K 15-Jun-12 0:22am    
I retrieved it but using only temp table..

1 solution

SQL
SET @assigneduserid = (select top(1) P.assigneduserid
(SELECT  TM.ticketnumber
	,TM.status 				  
	,TM.priority 
	,TD.createddate
	,TD.subject
	,TD.message
	,PU.Name
	,TM.createduserid AS createduserid
	,TC.name AS CategoryName
	,TC.id AS CategoryID
	,PU.Email AS CreatedUserEmail
	,PU1.Email AS AssignedUserEmail
	,TM.assigneduserid AS AssignedTo
	,TA.filename AS Attachment
	,TD.isprivatenote AS PrivateNote
	,TS.emailaddress AS managerMail
	
FROM  ticketmaster TM 
INNER JOIN ticketdetail TD ON TM.id=TD.ticketmasterid 
AND TD.createddate=(SELECT MIN(ticketdetail.createddate) 
FROM ticketdetail  WHERE ticketdetail.ticketmasterid=TM.id)
LEFT JOIN PortalUser PU ON PU.ID=TM.createduserid AND PU.Status=1
LEFT JOIN PortalUser PU1 ON PU1.ID=TM.assigneduserid AND PU1.Status=1
INNER JOIN ticketcategory TC ON TC.id=TM.categoryid AND TC.isactive=1 
LEFT JOIN ticketattachment TA ON TA.ticketdetailid=TD.id
INNER JOIN ticketsettings TS ON TS.DistributorID=@CompanyID OR TS.ResellerID=@CompanyID 
OR TS.BusinessID=@CompanyID OR TS.BusinessSiteID=@CompanyID
WHERE TM.id=@TicketID) as P);
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900