Keeping out the NULL Values - 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)

Keeping out the NULL Values

Author

Topic

Verduveltje
Starting Member

4 Posts

Posted - 2012-05-07 : 06:12:04

Hi there,

I have the following query:


SELECT M.* FROM Tags Y 
LEFT JOIN (SELECT TagCode FROM TagLookup WHERE IsCurrent = 'TRUE') AS M ON M.TagCode = Y.TagCode

This returns the following dataset:


NULL	24
69	1
CCW1	3
CCW3	1
CCW4	1
Cursus1	1
PMW	2
TEST	1

Now i dont want to see any NULL value, however I cannot use "WHERE M.TagCode IS NOT NULL" Because this query is part of a bigger query and I dont want any filtering in that.

any idea's?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-05-07 : 06:44:03

i dont want to see any NULL value
this query is part of a bigger query
I dont want any filtering in that

good luck

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-07 : 06:53:46

Then turn this query into a derived table, use the WHERE NOT NULL, and join on the results.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Verduveltje
Starting Member

4 Posts

Posted - 2012-05-07 : 07:16:26

quote:
Originally posted by webfred

i dont want to see any NULL value
this query is part of a bigger query
I dont want any filtering in that

good luck

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

Let me explain.

The problem im currently having is that my main query now shows:
blabla 123 ahf NULL
blabla 123 ahf CCW1

Where it should just show the 2nd row and not the one with NULL as its the exact same record... so my dataset should not show contain the NULL record, but just the CCW1 record.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-07 : 07:36:38

You can do the following:

SELECT
   blablaColumn,
   123Column,
   ahfColumn,
   MAX(CCWOrNULLColumn)
FROM
   YourTable
GROUP BY
   blablaColumn,
   123Column,
   ahfColumn

However, that may not be what you want. For example, what do you want to get if your data is like this?

blabla  123 ahf NULL
blabla  123 ahf CCW1

abcdef  457 xyz NULL

rrrrrr NULL NULL bbb
rrrrrr NULL NULL ccc
rrrrrr NULL NULL NULL

Or perhaps some other combination? The way you query would change depending on what your requirements are - hence Fred's request to post unfiltered query.

I have found Brett's blog to be very useful when I want to post in a way that gets quick and accurate answers: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources