not showing nulls

Question

not showing nulls

rs-337036

Ten Centuries

Points: 1105
More actions
February 1, 2007 at 12:57 pm

#115499

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. It is (EAV model). I know it is messy.
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]
&nbsp ON [PRIMARY] ,
CONSTRAINT [FK_ProfilePropertyDefinition_Portals] FOREIGN KEY
(
  [PortalID]
&nbsp REFERENCES [Portals] (
  [PortalID]
&nbsp 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]
&nbsp ON [PRIMARY] ,
CONSTRAINT [FK_UserProfile_ProfilePropertyDefinition] FOREIGN KEY
(
  [PropertyDefinitionID]
&nbsp REFERENCES [ProfilePropertyDefinition] (
  [PropertyDefinitionID]
&nbsp ON DELETE CASCADE ,
CONSTRAINT [FK_UserProfile_Users] FOREIGN KEY
(
  [UserID]
&nbsp REFERENCES [Users] (
  [UserID]
&nbsp 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]
&nbsp ON [PRIMARY] ,
CONSTRAINT [IX_Users] UNIQUE NONCLUSTERED
(
  [Username]
&nbsp ON [PRIMARY]
) ON [PRIMARY]
GO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Do you want to exclude the entire record is any field, (column) is NULL or just the individual NULLs?

If it is the individual NULLs, then use the ISNULL() function. Otherwise, you will need to have constraints, (in your WHERE clauses) that omit records where NULL.

Or, is this an artifact of the EAV design and you are trying to overcome that aspect? If so, could you supply a set of data that returns records with NULLs so we can work with that set?

Thanks

I wasn't born stupid - I had to study.

rs-337036 Ten Centuries Points: 1105 More actions · Answer 2

rs-337036

Ten Centuries

Points: 1105

February 1, 2007 at 4:22 pm

#687261

Thanks for your help. used it in the where clause.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

If you need only lines where ALL profiles are present you may use inner join:

SELECT FN.propertyValue AS FirstName, LN.propertyValue AS LastName,

CN.propertyValue AS CompanyName, C.propertyValue AS City, U.UserID, U.Email AS email

FROM Users U

INNER JOIN UserProfile AS up ON up.UserID = U.UserID

INNER JOIN ProfilePropertyDefinition AS FN ON up.PropertyDefinitionID = FN.PropertyDefinitionID AND FN.propertyName = 'FirstName'

INNER JOIN ProfilePropertyDefinition AS LN ON up.PropertyDefinitionID = LN.PropertyDefinitionID AND LN.propertyName = 'LastName'

INNER JOIN ProfilePropertyDefinition AS CN ON up.PropertyDefinitionID = CN.PropertyDefinitionID AND CN.propertyName = 'Company Name'

INNER JOIN ProfilePropertyDefinition AS C ON up.PropertyDefinitionID = C.PropertyDefinitionID AND C.propertyName = 'City'

Must perform much faster.

_____________
Code for TallyGenerator