Not showing NULLS - 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 2000 Forums

SQL Server Development (2000)

Not showing NULLS

Author

Topic

soorma
Yak Posting Veteran

52 Posts

Posted - 2007-02-01 : 14:39:14

When i run this SQL Query i get nulls in some fields. I don't want to show records when any of the fields is null. Actually i got some help from the forum to make this query. The query wroks fine. But need to exclude nulls.

SELECT MAX(CASE WHEN propertyName = 'FirstName' THEN propertyValue END) AS FirstName,
MAX(CASE WHEN propertyName = 'LastName' THEN propertyValue END) AS LastName,
MAX(CASE WHEN propertyName = 'Company Name' THEN propertyValue END) AS CompanyName,
MAX(CASE WHEN propertyName = 'City' THEN propertyValue END) AS City,

d.UserID, Users.Email AS email
FROM (SELECT PropertyName, PropertyValue, UserID
FROM ProfilePropertyDefinition AS ppd INNER JOIN
UserProfile AS up ON up.PropertyDefinitionID = ppd.PropertyDefinitionID) d INNER JOIN
Users ON d.UserID = Users.UserID
GROUP BY d.UserID, Users.Email
ORDER BY d.UserID

Here are the Table Definations

CREATE TABLE [ProfilePropertyDefinition] (
[PropertyDefinitionID] [int] IDENTITY (1, 1) NOT NULL ,
[PortalID] [int] NULL ,
[ModuleDefID] [int] NULL ,
[Deleted] [bit] NOT NULL ,
[DataType] [int] NOT NULL ,
[DefaultValue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertyCategory] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PropertyName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Length] [int] NOT NULL CONSTRAINT [DF_ProfilePropertyDefinition_Length] DEFAULT (0),
[Required] [bit] NOT NULL ,
[ValidationExpression] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ViewOrder] [int] NOT NULL ,
[Visible] [bit] NOT NULL ,
CONSTRAINT [PK_ProfilePropertyDefinition] PRIMARY KEY CLUSTERED
(
[PropertyDefinitionID]
) ON [PRIMARY] ,
CONSTRAINT [FK_ProfilePropertyDefinition_Portals] FOREIGN KEY
(
[PortalID]
) REFERENCES [Portals] (
[PortalID]
) ON DELETE CASCADE
) ON [PRIMARY]
GO

CREATE TABLE [UserProfile] (
[ProfileID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[PropertyDefinitionID] [int] NOT NULL ,
[PropertyValue] [nvarchar] (3750) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertyText] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Visibility] [int] NOT NULL CONSTRAINT [DF__UserProfi__Visib__0D99FE17] DEFAULT (0),
[LastUpdatedDate] [datetime] NOT NULL ,
CONSTRAINT [PK_UserProfile] PRIMARY KEY NONCLUSTERED
(
[ProfileID]
) ON [PRIMARY] ,
CONSTRAINT [FK_UserProfile_ProfilePropertyDefinition] FOREIGN KEY
(
[PropertyDefinitionID]
) REFERENCES [ProfilePropertyDefinition] (
[PropertyDefinitionID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_UserProfile_Users] FOREIGN KEY
(
[UserID]
) REFERENCES [Users] (
[UserID]
) ON DELETE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IsSuperUser] [bit] NOT NULL CONSTRAINT [DF_Users_IsSuperUser] DEFAULT (0),
[AffiliateId] [int] NULL ,
[Email] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DisplayName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Users_DisplayName] DEFAULT (''),
[UpdatePassword] [bit] NOT NULL CONSTRAINT [DF_Users_UpdatePassword] DEFAULT (0),
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY] ,
CONSTRAINT [IX_Users] UNIQUE NONCLUSTERED
(
[Username]
) ON [PRIMARY]
) ON [PRIMARY]
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-01 : 15:01:32

The query look bloated. Try this

SELECT		MAX(CASE WHEN xx.propertyName = 'FirstName' THEN xx.propertyValue END) AS FirstName, 
		MAX(CASE WHEN xx.propertyName = 'LastName' THEN xx.propertyValue END) AS LastName, 
		MAX(CASE WHEN xx.propertyName = 'Company Name' THEN xx.propertyValue END) AS CompanyName, 
		MAX(CASE WHEN xx.propertyName = 'City' THEN xx.propertyValue END) AS City,
		xx.UserID,
		u.Email
FROM		ProfilePropertyDefinition AS ppd
INNER JOIN	UserProfile AS up ON up.PropertyDefinitionID = ppd.PropertyDefinitionID
INNER JOIN	Users AS u ON u.UserID = xx.UserID 
GROUP BY	xx.UserID,
		u.Email
ORDER BY	xx.UserID

You must change the xx column prefix to the appropriate!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-01 : 15:06:33

[code]SELECT FirstName,
LastName,
CompanyName,
City,
UserID,
Email
FROM (
SELECT MAX(CASE WHEN xx.propertyName = 'FirstName' THEN xx.propertyValue END) AS FirstName,
MAX(CASE WHEN xx.propertyName = 'LastName' THEN xx.propertyValue END) AS LastName,
MAX(CASE WHEN xx.propertyName = 'Company Name' THEN xx.propertyValue END) AS CompanyName,
MAX(CASE WHEN xx.propertyName = 'City' THEN xx.propertyValue END) AS City,
xx.UserID,
u.Email
FROM ProfilePropertyDefinition AS ppd
INNER JOIN UserProfile AS up ON up.PropertyDefinitionID = ppd.PropertyDefinitionID
INNER JOIN Users AS u ON u.UserID = xx.UserID
GROUP BY xx.UserID,
u.Email
) AS d
WHERE FirstName IS NOT NULL
AND LastName IS NOT NULL
AND CompanyName IS NOT NULL
AND City IS NOT NULL
AND UserID IS NOT NULL
AND Email IS NOT NULL
ORDER BY UserID[/code]

Peter Larsson
Helsingborg, Sweden

soorma
Yak Posting Veteran

52 Posts

Posted - 2007-02-01 : 18:18:48

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources