Query an expression and display not null results

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

General SQL Server Forums

New to SQL Server Programming

Query an expression and display not null results

Author

Topic

rreif
Starting Member

4 Posts

Posted - 2010-08-18 : 15:50:23

Hi all,

This is a relatively simple request, but I can't seem to figure it out

I want only display the results in a subquery that are not null. The query is below:

SELECT AssessmentSummaryInformation.AssessmentID, AssessmentSummaryInformation.AssessmentName,
(SELECT TOP 1 Partners.PartnerName
FROM Partners Inner Join PartnersAndAssessments On Partners.PartnerID = PartnersAndAssessments.PartnerID
WHERE AssessmentSummaryInformation.AssessmentID = PartnersAndAssessments.AssessmentID AND Partners.PartnerName = 'AoG') as 'PartnerName', Countries.Country
FROM AssessmentSummaryInformation INNER JOIN
Countries ON AssessmentSummaryInformation.CountryID = Countries.CountryID_UN_Standard

WHERE 'PartnerName' IS NOT NULL

The results show all the results in the PartnerName field. How can I get it to not display the NULL results?

I have included a simpler query below to troubleshoot, which also doesn't work...

SELECT AssessmentID, AssessmentName, GPSLatitude as 'GPS'
FROM AssessmentSummaryInformation
WHERE 'GPS' IS NOT NULL

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-18 : 16:16:10

your query can not have aliases in WHERE condition.
try this:


SELECT AssessmentID, AssessmentName, GPSLatitude as 'GPS'
FROM AssessmentSummaryInformation
WHERE GPSLatitude IS NOT NULL

rreif
Starting Member

4 Posts

Posted - 2010-08-18 : 17:56:24

Thanks for the response slimt. Hmm..then is it possible to only show results that aren't null from a subquery?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-19 : 14:07:24

why do you need subquery in first place? cant you change into inner join so that you wont get the NULLs at all?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rreif
Starting Member

4 Posts

Posted - 2010-08-20 : 09:02:30

There is a one-to-many relationship and I only want to display one record per primary key. That is why the "TOP 1" statement is there.

With the inner join, it would display a project record for each different partner, which is too much information. In my case, I am trying to limit the results displayed to 1 project record per query, even though there are multiple partners per project. To do this, I have included a subquery, which works. Unfortunately when I try to include a WHERE statement in the subquery to only display projects with a specific partner, it displays all projects with the partner as null where there is not matching partner.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 11:11:01

quote:
Originally posted by rreif

There is a one-to-many relationship and I only want to display one record per primary key. That is why the "TOP 1" statement is there.

With the inner join, it would display a project record for each different partner, which is too much information. In my case, I am trying to limit the results displayed to 1 project record per query, even though there are multiple partners per project. To do this, I have included a subquery, which works. Unfortunately when I try to include a WHERE statement in the subquery to only display projects with a specific partner, it displays all projects with the partner as null where there is not matching partner.

then you can use APPLY operator to get only TOP 1 record. But for that you need to have sql 2005 or more.
see scenario 2

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rreif
Starting Member

4 Posts

Posted - 2010-08-20 : 11:38:56

quote:
Originally posted by visakh16

then you can use APPLY operator to get only TOP 1 record. But for that you need to have sql 2005 or more.
see scenario 2

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Simply amazing! It works great. Thanks!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 11:43:09

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources