CODEWITHSUNDEEP
sqlsql-servert-sql
Stpete111
Stpete111

Reputation: 3457

SQL Query - add NOT NULL condition to either of two columns

I am creating a stored procedure in SQL Server as follows:

CREATE PROCEDURE [dbo].[SearchData] 
    @searchText varchar(500)
AS
BEGIN
    SET NOCOUNT ON

    SELECT TOP 50 
        s.[MaxRank] AS [Rank],
        sc.TaskSourceId,
        c.IndividualName,
        c.EntityName,
        c.Text
    FROM
        (SELECT
             MAX(s.Rank) AS MaxRank,
             MAX(c.CaptureId) AS MaxCaptureId,
             c.TaskSourceId
         FROM
             FreeTextTable(Data.SearchData,
                  (Identifier, IndividualName, EntityName, [Text]), @searchText) s
         JOIN
             Data.Capture c ON s.[Key] = c.CaptureId
         GROUP BY
             c.TaskSourceId) s
    JOIN
        Data.Capture c ON c.CaptureId = s.MaxCaptureId
    JOIN
        Data.Source sc ON c.TaskSourceId = sc.TaskSourceId
    ORDER BY
        s.MaxRank DESC
END

The above executes successfully but includes some results where the Text field is the only field with data, with the others being NULL. I want to add a condition that says that, in the returned results, IndividualName must not be null OR EntityName must not be null.

I'm having trouble figuring where to put this and the exact syntax.

Upvotes: 1

Views: 2878

Answers (3)

MDiesel
MDiesel

Reputation: 2667

You can do an isnull on both columns and check to make sure that value is not null, like this:

CREATE PROCEDURE [dbo].[SearchData] 
@searchText varchar(500)

AS BEGIN SET NOCOUNT ON

select top 50 s.[MaxRank] as [Rank],
    sc.TaskSourceId,
    c.IndividualName,
    c.EntityName,
    c.Text

from    (
    select  max(s.Rank) as MaxRank,
        max(c.CaptureId) as MaxCaptureId,
        c.TaskSourceId
    from    FreeTextTable(Data.SearchData,
           (Identifier, IndividualName, EntityName, [Text]),
               @searchText) s
    join    Data.Capture c
    on  s.[Key] = c.CaptureId
    group   by c.TaskSourceId
    ) s
join    Data.Capture c
on  c.CaptureId = s.MaxCaptureId
join   Data.Source sc
on     c.TaskSourceId = sc.TaskSourceId
where not isnull(c.IndividualName, c.EntityName) is null  
order   by s.MaxRank desc
END

Upvotes: 2

DVT
DVT

Reputation: 3127

You can also use these condition in the join condition.

CREATE PROCEDURE [dbo].[SearchData] 
@searchText varchar(500)

AS BEGIN SET NOCOUNT ON

select top 50 s.[MaxRank] as [Rank],
    sc.TaskSourceId,
    c.IndividualName,
    c.EntityName,
    c.Text
from    (
    select  max(s.Rank) as MaxRank,
        max(c.CaptureId) as MaxCaptureId,
        c.TaskSourceId
    from    FreeTextTable(Data.SearchData,
           (Identifier, IndividualName, EntityName, [Text]),
               @searchText) s
    join    Data.Capture c
    on  s.[Key] = c.CaptureId
    group by c.TaskSourceId
    ) s
join    Data.Capture c
on  c.CaptureId = s.MaxCaptureId AND (c.IndividualName IS NOT NULL OR c.EntityName IS NOT NULL)
join   Data.Source sc
on     c.TaskSourceId = sc.TaskSourceId
order by s.MaxRank desc

END

Upvotes: 0

JohnHC
JohnHC

Reputation: 11205

I assume you mean that one of the fields CAN be null. If so:

CREATE PROCEDURE [dbo].[SearchData] 
@searchText varchar(500)

AS BEGIN SET NOCOUNT ON

select top 50 s.[MaxRank] as [Rank],
    sc.TaskSourceId,
    c.IndividualName,
    c.EntityName,
    c.Text

from    (
    select  max(s.Rank) as MaxRank,
        max(c.CaptureId) as MaxCaptureId,
        c.TaskSourceId
    from    FreeTextTable(Data.SearchData,
           (Identifier, IndividualName, EntityName, [Text]),
               @searchText) s
    join    Data.Capture c
    on  s.[Key] = c.CaptureId
    group   by c.TaskSourceId
    ) s
join    Data.Capture c
on  c.CaptureId = s.MaxCaptureId
join   Data.Source sc
on     c.TaskSourceId = sc.TaskSourceId
where not (c.IndividualName is null and c.EntityName is null) -- excludes results where both are null
order   by s.MaxRank desc
END

Upvotes: 2

Related Questions