In a sub-query that returns multiple results, is there a way to take those results and return them as a list so the query doesn't fail?

The error I'm getting is:
SQL Server Database Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

This sub-query is within the SELECT portion of the statement and I want to have all of the results returned show as one string - Does anyone know if this is even possible?

    Perhaps you want to use the IN keyword, e.g., IN (SELECT colname FROM tablename).

      No - I want the multiple results (the subquery is in the SELECT portion of the query) and I want them returned, seperated by commas, as one field

        Oh, now I understand. It may be possible, but what's wrong with retrieving the results normally and then building the comma separated field in PHP?

          I may have to go that direction.. at the very least building all of the logic into a Stored Procedure (I'm working with MS SQL).

          I prefer to put as much logic into the DB side as possible as I have found significant performance gains in doing so.

          The query as it stands (without the sub query we're speaking about) is as follows:

          SELECT 
	pms.type_name AS pms_type_name, 
	pmct.type_name AS pmct_type_name, 
	pmc.prj_code, 
	pmc.Phases, 
	se.emp_alpha_name, 
	ccom.company_name, 
	pmc.location_code, 
	replace(str(month(pmc.WrittenDate),2),' ','0')+'-'+replace(str(day(pmc.WrittenDate),2),' ','0')+'-'+datename(yyyy,pmc.WrittenDate) AS writtendate, 
	pmc.CID, 
	swr.sw_id, 
	swr.sw_approvalstage, 
	swr.sw_archived, 
	pmc.ContractProposal, 
	cc.fname, 
	cc.lname, 
	ccom.company_name, 
	pmp.prj_name, 
	COUNT(pmcsc.contractSubConsultants_idx) AS sub_consultant_count, 
	SUM(CAST(pmcsc.flag_sub_consultant_certificate_of_insurance AS INT)) AS sub_consultant_cert_of_insurance, 
	SUM(CAST(pmcsc.flag_sub_consultant_agreement AS INT)) AS sub_consultant_agreement 
FROM 
	PM_Contracts pmc 
	INNER JOIN 
		CC_contacts cc 
		ON (pmc.contact_uid = cc.contact_idx) 
	INNER JOIN 
		PM_CNPTypes pmct 
		ON (pmc.cnp_type_idx = pmct.type_idx) 
	INNER JOIN 
		PM_Status pms 
		ON (pmc.status_idx = pms.type_idx) 
	INNER JOIN 
		PM_Projects pmp 
		ON (pmp.prj_code = pmc.prj_code) 
	INNER JOIN 
		CC_Companies ccom 
		ON (cc.company_idx = ccom.company_idx) 
	INNER JOIN 
		System_empinfo se 
		ON (pmc.contract_pm = se.emp_code) 
	LEFT OUTER JOIN 
		PM_LinkForm lf 
		ON(lf.CID = pmc.CID) 
	LEFT OUTER JOIN 
		PM_StartWorkReq swr 
		ON (lf.sw_id = swr.sw_id) 
	LEFT JOIN 
		PM_ContractSubConsultants pmcsc 
		ON (pmc.CID = pmcsc.contract_CID) 
WHERE 
	( 
		pmc.status_idx in(2,3,9) 
		OR 
		( 
			SELECT 
				SUM(CAST(pmcsc.flag_sub_consultant_certificate_of_insurance AS INT)) 
			FROM 
				PM_ContractSubConsultants pmcsc 
			WHERE 
				pmcsc.contract_CID = pmc.CID 
		) > 0 
		OR 
		( 
			SELECT 
				SUM(CAST(pmcsc.flag_sub_consultant_agreement AS INT)) 
			FROM 
				PM_ContractSubConsultants pmcsc 
			WHERE 
				pmcsc.contract_CID = pmc.CID 
		) > 0 
	) 
GROUP BY 
	pms.type_name, 
	pmct.type_name, 
	pmc.prj_code, 
	pmc.Phases, 
	se.emp_alpha_name, 
	ccom.company_name, 
	pmc.location_code, 
	replace(str(month(pmc.WrittenDate),2),' ','0')+'-'+replace(str(day(pmc.WrittenDate),2),' ','0')+'-'+datename(yyyy,pmc.WrittenDate), 
	pmc.CID, 
	swr.sw_id, 
	swr.sw_approvalstage, 
	swr.sw_archived, 
	pmc.ContractProposal, 
	cc.fname, 
	cc.lname, 
	ccom.company_name, 
	pmp.prj_name

          The subquery would be on PM_ContractSubConsultants, retrieving all of the contents of the field 'phase' for all matches, separated by commas.

          From what I've seen while researching this over the last hour is that it's either impossible or no one's bothered to try. My assumption would be the former.

          SO unless anyone has any ideas, I'm going to move forward to building this as a stored procedure utilizing temp tables to aggregate the data.

            I prefer to put as much logic into the DB side as possible as I have found significant performance gains in doing so.

            Yes, that is generally the way to go. However, in this case I daresay that it is not so much the logic of the query itself that you are trying to write, but that of the presentation, so I would be more willing to use PHP to process the results retrieved.

              I wrote it into a stored procedure AND wrote it on the PHP side. On my test server the stored procedure shaves 1/10 of a second off of the PHP processing time, which usually equates to a full second shaved off on the live server under heavy load.

              I usually test multiple solutions to find the best performance gain as heavy load on a server can cause different aspects of an application respond in very unexpected ways.

              Thanks for trying to help - I'm just going to have to hope and pray Microsoft considers this as a worthwhile addition to MS SQL's capabilities. Either that or wait patiently while MySQL slowly gains more and more of the capabilities and stability of MS SQL. I think 'leaps and bounds' is a good description of their progress over the last decade.

                Write a Reply...