sub select only returns one record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

sub select only returns one record

Author

Topic

lanamor
Starting Member

8 Posts

Posted - 2011-01-05 : 11:32:38

Hello all, I'm stuck on a bit of SQL query. Below is my syntax. The problem is that the C1.Name column only returns one record. I assume it's a problem with my sub select. Anyone have any ideas what's wrong? I apologize if I've left out any info you might need; please ask for more if need be.

SELECT 	v_Advertisement.AdvertisementName,
	C1.Name AS [Limit to Collection],
	v_Advertisement.Comment, 
	v_Collection.Name AS [Collection], 
	AdvertisementID
FROM v_Advertisement
left JOIN v_Collection ON v_Advertisement.CollectionID = v_Collection.CollectionID
left JOIN
	(
	 SELECT v_Collection.Name
	 FROM v_CollectionRuleQuery
	 JOIN v_Collection ON v_CollectionRuleQuery.LimitToCollectionID = v_Collection.CollectionID
	) C1 ON v_Collection.Name = C1.Name
ORDER BY v_Advertisement.AdvertisementName

Output:
Advertisement Name          Limit to Collection            Collection                      Advertisement Id  
-----------------------------------------------------------------------------------------------------------
Adaptiva Companion          App Deploy Stage 3 (C2SD)      Update to Companion             FCS2000B  
Adobe Acrobat 9.4.1                                        Update to Adobe Acrobat 9.4.1   FCS20041  
Adobe Flash 10.1.102.64                                    App Deploy Stage 2              FCS20038  
Adobe Pro (Silent)                                         Upgrade to Adobe Acrobat 9.x    FCS2004D  
Adobe Reader 9.4.1                                         Update to Adobe Reader 9.4.1    FCS20040

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-05 : 13:38:04

Does the subquery executed by itself return proper results?

These all appear to be views, so you may have to examine the code inside of v_CollectionRuleQuery. it would appear that it is not returning results for "Update to Adobe Acrobat 9.4.1" ,"App Deploy Stage 2" ,"Upgrade to Adobe Acrobat 9.x" , or "Update to Adobe Reader 9.4.1 " which means it could be that view instead causing the issue.

Make sure your subquery actually returns v_Collection.Name for those values first...

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources