Sub select - SQL Server Forums

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

Author

Topic

lanamor
Starting Member

8 Posts

Posted - 2010-12-06 : 09:16:23

Hello all,

I'm having issues with the below query. When I run it I get no results. I believe the problem is due to v_CollectionRuleQuery.LimitToCollectionID being NULL for some entries. I'd likd to list records even if the LimitToCollection is NULL. How would I handle this?

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-06 : 09:22:02

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

This will give a null C1.Name for those missing so you might want to add v_Collection.Name to the resultset.
oh - it's already there.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lanamor
Starting Member

8 Posts

Posted - 2010-12-06 : 09:28:39

Thanks nigelrivett for the quick reply. I still receieve no results. Is there more information I can include to assist?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-06 : 09:43:38

Maybe this:

quote:
Originally posted by lanamor

Hello all,

I'm having issues with the below query. When I run it I get no results. I believe the problem is due to v_CollectionRuleQuery.LimitToCollectionID being NULL for some entries. I'd likd to list records even if the LimitToCollection is NULL. How would I handle this?

SELECT v_Advertisement.AdvertisementName,
C1.Name AS 'Limit to Collection',
v_Advertisement.Comment,
v_Collection.Name AS 'Collection',
AdvertisementID
FROM v_Advertisement
INNER JOIN v_Collection ON v_Advertisement.CollectionID = v_Collection.CollectionID
INNER JOIN
(
SELECT v_Collection.Name
FROM v_Collection
INNER JOIN v_CollectionRuleQuery ON v_Collection.CollectionID = v_CollectionRuleQuery.CollectionID
WHERE (v_CollectionRuleQuery.LimitToCollectionID IS NULL OR v_Collection.CollectionID = v_CollectionRuleQuery.LimitToCollectionID)
) C1 ON v_Collection.Name = C1.Name
ORDER BY v_Advertisement.AdvertisementName

No, you're never too old to Yak'n'Roll if you're too young to die.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-06 : 10:43:19

Then it's not due to v_CollectionRuleQuery.
try

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_Collection
INNER JOIN v_CollectionRuleQuery ON v_Collection.CollectionID = v_CollectionRuleQuery.CollectionID
WHERE v_Collection.CollectionID = v_CollectionRuleQuery.LimitToCollectionID
) C1
ON v_Collection.Name = C1.Name
ORDER BY v_Advertisement.AdvertisementName

That should give a clue as to where you are losing rows.
You'll get nulls from the tables which do not have data.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lanamor
Starting Member

8 Posts

Posted - 2010-12-06 : 11:00:31

Thank you both for jumping in. I don't think I've given you good enough info to help me though. Here are my tables with the primary key listed first for each.

v_Advertisement:
AdvertisementID, AdvertisementName, Comment, CollectionID

v_Collection:
CollectionID, Name

v_CollectionRuleQuery:
QueryID, CollectionID, LimitToCollectionID

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-06 : 11:35:04

Think the problem is your description of the result and desired result.
The last query I gave will include all rows from v_Advertisement whether of not there are matching rows from the other tables.
The previos one included rows from v_Advertisement where there were matching rows in v_Collection.

Not sure what you want if it's not that.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lanamor
Starting Member

8 Posts

Posted - 2010-12-06 : 12:06:58

OK how about this. Below is some data from the tables and what I'd like returned. Maybe this will better explain my weak question. Forgive me for being difficult.

Table v_Advertisement:
 AdvertisementName			Comment			AdvertisementId		CollectionID  
  Adaptiva Companion      					 FCS2000B   		 FCS00012  
  Adobe Acrobat 9.4.1      					 FCS20041   		 FCS00046  
  Adobe Flash 10.1.102.64   		 startdate: 11/23/10   	 FCS20038   		 FCS00013  
  Adobe Reader 9.4.1      					 FCS20040   		 FCS00043  
  Adobe Shockwave 11.5.9.615      				 FCS20044   		 FCS00045  
  Apple Quicktime 7.68.75   		 startdate:10/01/10   	 FCS20013   		 FCS0000F  


Table v_Collection:
 CollectionID		Name  
  FCS0000D   	 	 Forscom Application Updates  
  FCS0000F   	 	 App Deploy All Workstations  
  FCS00010   	  	 App Deploy Stage 5 (G1,G2,G4,G6)  
  FCS00011   	 	 App Deploy Stage 4(G6)  
  FCS00012   	 	 App Deploy Stage 3 (C2SD)  
  FCS00013   	 	 App Deploy Stage 2 (Contractors)  
  FCS00014   	  	 App Deploy Stage 1 (SMC)  
  FCS00042   		 Update to Lotus Notes Viewer 7.6.1.123  
  FCS00043   		 Update to Adobe Reader 9.4.1  
  FCS00044   		 Update to Mozilla FireFox 3.6.12  

  

Table v_CollectionRuleQuery:
 CollectionID		LimitToCollectionID  
  FCS0000F   		 SMS00004  
  FCS00010     
  FCS00011     
  FCS00012     
  FCS00013     
  FCS0002A     
  FCS0002B     
  FCS00035   		 FCS0000F  
  FCS0003A   		 FCS00012  
  FCS0003C     
  FCS0003F     
  FCS00040   		 FCS0000F  
  FCS00041   		 FCS00011  
  FCS00042   		 FCS00011  
  FCS00043   		 FCS00011  
  FCS00044   		 FCS00011

What I want returned from query:
v_Advertisement.AdvertisementName,
v_CollectionRuleQuery.LimitToCollectionID AS 'Collection Limit', << Convert to v_Collection.Name.. i.e JOIN
v_Advertisement.Comment,
v_Advertisement.CollectionID AS 'Collection'<< Convert to v_Collection.Name.. i.e JOIN

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-06 : 12:42:08

The last query I gave should give you
v_Advertisement.AdvertisementName
_Advertisement.Comment
Collection Limit if there is one
Collection if there is one

Actually it should be
SELECT v_Advertisement.AdvertisementName,
C1.Name AS [Limit to Collection],
v_Advertisement.Comment,
v_Collection.Name AS [Collection],
AdvertisementID

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lanamor
Starting Member

8 Posts

Posted - 2010-12-06 : 12:58:31

nigelrivett,

Using the below query returns nothing in the C1.Name column. Other columns are correct.

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_Collection
	 INNER JOIN v_CollectionRuleQuery ON v_Collection.CollectionID = v_CollectionRuleQuery.CollectionID
	 WHERE v_Collection.CollectionID = v_CollectionRuleQuery.LimitToCollectionID
	) C1 ON v_Collection.Name = C1.Name
ORDER BY v_Advertisement.AdvertisementName

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-12-06 : 13:37:01

quote:
Originally posted by lanamor

Thank you both for jumping in. I don't think I've given you good enough info to help me though. Here are my tables with the primary key listed first for each.

Please post real DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

lanamor
Starting Member

8 Posts

Posted - 2010-12-06 : 14:12:10

jcelko,
Thank you for your thoughts; however if I knew what Declarative Referential Integrity was I would most likely not be posting my question here. I understand if you do not wish to contribute to this thread because it might be too difficult for you to help me through my problem while I am obviously not an SQL expert such as you.

Understand that I am not an SQL expert nor do I need to be. This SQL database is a backend for System Center Configuration Manager. Very rarely will I ever need to compose SQL queries as complex as this.

If you would like to contribute I would appreciate it greatly, but if you simply want to spank my hand because I don't speak SQL nativly then you have my permission to not reply.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources