?
Solved

Search On NULL

Posted on 2002-05-28
18
Medium Priority
?
313 Views
Last Modified: 2012-08-14
I have a search being performed on a join.  The two tables are A and B.  A record always exists in A and has a 1 to 1 relationship with B.  However not all records in A are forced ro have a record in B.  So when I do the join some of the columns in B are showing as NULL.

Now the problem is that I am doing a wildcard search on oen of the columns in B such that if the wildcard is blank I would like this to come back as true but it seems to basically not do any checking against NULL values.

Does this make sense?  If not I'll post more info.

Cheers

Dave
0
Comment
Question by:daveamour
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 3
  • +3
18 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7038806
does this help?
...
WHERE
....
AND (b.yourcol LIKE @yourparam OR @yourparam IS NULL)

CHeers
0
 
LVL 19

Author Comment

by:daveamour
ID: 7038822
That was the first thing I tried but that doesnt help as If I have a search for 1 field in table A and another in table B needing both to match I will get results where only the first field matches since the second field, in rows which don't have a matching record in B, will always be NULL.

Thanks anyway

Dave
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7038829
Indeed, you should post some samples of data and of the expected return, regarding to the parameters...
CHeers
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 19

Author Comment

by:daveamour
ID: 7038841
Ok perhaps I can rephrase the question.

If I do a wildcard search on a field which contain NULL and my wildcard is Like '%%' - ie should normally return true whatever the data then in the case of a NULL it returns false but I need it to return true.

Does this make more sense?

Dave
0
 
LVL 2

Expert Comment

by:Carlovski
ID: 7038843
I am a little confused,
You want to search based on a field in table B, but you want it to match where there is no Row in table B?
Do you want it to ignore the condition on table B if there is now matching row? I.e select the relevant fields from A and B where there is a matching field in A, and if there is a related record in B , that it also has a matching field?

If so, try this

SELECT A.myfield1,
       A.myfield1,
       B.myfield1,
       B.myfield2
From TableA A
Outer join tableb B on A.id = B.id
Where A.myfield1 like 'mywildcardsearch'
AND   ((b.myfield like 'mywildcardsearch') OR (b.myfield IS NULL))

Carl
0
 
LVL 19

Author Comment

by:daveamour
ID: 7038845
Carl, this wont work for the same reason as angelIII's first suggestion.

Thanks

Dave
0
 
LVL 19

Author Comment

by:daveamour
ID: 7038854
Ok heres some more info

Here is my join with no Where Clause

SELECT
     UserLogin.UserID,
     UserLogin.RegDateTime,
         UserLogin.EmailAddress,
     UserLogin.Password,
         UserLogin.SecretQuestion,
     UserLogin.SecretAnswer,
         UserDetails.Title,
     UserDetails.FirstName,
        UserDetails.Surname    
     FROM
     UserDetails RIGHT OUTER JOIN
     UserLogin ON UserDetails.UserID = UserLogin.UserID

And this returns

1
2002-05-24 10:10:32.000          password     sq     sa     MR     Darren     Scrine     2
2002-05-28 09:50:04.000     darren.scrine@pool.net     password     mums maiden name     jackson     MR     Darren     Scrine     5
2002-05-28 09:39:00.000     billy.johal@pool.net     password     secret question     secret answer     MR     Billy     Johal     6
2002-04-29 14:33:10.000     1@1.com     password     1     2     MR     a     e     10
2002-05-28 09:01:08.000     david.amour@pool.net     daveamour               NULL     NULL     NULL     11
2002-05-24 11:08:34.000     bill.bloggs@pool.net     123456               NULL     NULL     NULL     7
2002-04-29 14:55:36.000     2@2.com     password     dog's name     fudgie     MR     q     q     8
2002-04-29 14:56:18.000     3@3.com     password               NULL     NULL     NULL     9
2002-04-29 15:02:33.000     4@4.com     password     sq     sa     MR     q     q    

Now I want the following select to return all records where the EmailAddress matches da but it doesnt return the ones with NULL in the Surname etc.

SELECT
     UserLogin.UserID,
     UserLogin.RegDateTime,
         UserLogin.EmailAddress,
     UserLogin.Password,
         UserLogin.SecretQuestion,
     UserLogin.SecretAnswer,
         UserDetails.Title,
     UserDetails.FirstName,
        UserDetails.Surname    
     FROM
     UserDetails RIGHT OUTER JOIN
     UserLogin ON UserDetails.UserID = UserLogin.UserID
     WHERE
     EmailAddress LIKE '%' + 'da'  + '%'
     AND (Surname LIKE  '%' + ''   + '%' )
     AND (Postcode LIKE  '%' + ''  + '%')

I could try

     EmailAddress LIKE '%' + 'da'  + '%'
     AND (Surname LIKE  '%' + ''   + '%' Or Surname Is Null)
     AND (Postcode LIKE  '%' + ''  + '%' Or Postcode Is Null)

which would work here but wouldn't work if I was searching on an email addess and a surname.

Dave
0
 
LVL 2

Expert Comment

by:Carlovski
ID: 7038864
As far as I am aware it should work,
The outer join should produce a result set something like

A.myfield1 A.myfield2 B.myfield1 B.Myfield2
stuff       more       match!     match!
Thing       Thingy     <NULL>     <NULL>
stuff       another    stuff       more
stuff       more       <NULL>     <NULL>

The where clauses should then match on both the rows with data, or when it is null, i.e if your wildcard was  like '%stuff%' for both tables, you would get rows 1,3 and 4 back.

Of course I have probably got confused on exactly what the problem is! If you can post a few rows from your tables and what you expect, as angellll suggested that might help.

Cheers,
Carl
0
 
LVL 2

Expert Comment

by:Carlovski
ID: 7038904
Ah,
I get it now!
I don't get why your suggestion doesn't work though.
Where does the postcode field live?
You could try using ISNULL, ie change your where clauses to WHERE ISNULL(Surname,'') LIKE '%' + ''  + '%' so the NULLS are converted to blank strings. But this won't really do anything different to doing an IS Null check.

0
 
LVL 19

Author Comment

by:daveamour
ID: 7039010
The postcode lives in table B
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7039231
Maybe something like this.  I'm not sure how you're storing the wildcard value to be used in the WHERE test, so I used a "copycat" variable name:

DECLARE @EmailAddress VARCHAR(nn) --chg to correct length
DECLARE @Surname VARCHAR(nn) --chg to correct length
DECLARE @Postcode VARCHAR(nn) --chg to correct length
SELECT
    UserLogin.UserID,
    UserLogin.RegDateTime,
        UserLogin.EmailAddress,
    UserLogin.Password,
        UserLogin.SecretQuestion,
    UserLogin.SecretAnswer,
        UserDetails.Title,
    UserDetails.FirstName,
       UserDetails.Surname    
    FROM
    UserDetails RIGHT OUTER JOIN
    UserLogin ON UserDetails.UserID = UserLogin.UserID
    WHERE
    EmailAddress LIKE CASE WHEN @EmailAddress IS NULL OR @EmailAddress = '' THEN EmailAddress ELSE '%' + @EmailAddress  + '%' END
    AND (Surname LIKE CASE WHEN @Surname IS NULL OR @Surname = '' THEN Surname ELSE '%' + @Surname + '%' END
    AND (Postcode LIKE CASE WHEN @Postcode IS NULL OR @Postcode = '' THEN Postcode ELSE '%' + @Postcode + '%' END
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7040248
Oops, gotta' correct a few syntax errors:

DECLARE @EmailAddress VARCHAR(50) --chg to correct length
DECLARE @Surname VARCHAR(50) --chg to correct length
DECLARE @Postcode VARCHAR(50) --chg to correct length
SELECT
   UserLogin.UserID,
   UserLogin.RegDateTime,
       UserLogin.EmailAddress,
   UserLogin.Password,
       UserLogin.SecretQuestion,
   UserLogin.SecretAnswer,
       UserDetails.Title,
   UserDetails.FirstName,
      UserDetails.Surname    
   FROM
   UserDetails RIGHT OUTER JOIN
   UserLogin ON UserDetails.UserID = UserLogin.UserID
   WHERE
   EmailAddress LIKE CASE WHEN @EmailAddress IS NULL OR @EmailAddress = '' THEN EmailAddress ELSE '%'
+ @EmailAddress  + '%' END
   AND (Surname LIKE CASE WHEN @Surname IS NULL OR @Surname = '' THEN Surname ELSE '%' + @Surname +
'%' END)
   AND (Postcode LIKE CASE WHEN @Postcode IS NULL OR @Postcode = '' THEN Postcode ELSE '%' + @Postcode
+ '%' END)
0
 

Expert Comment

by:nghlan
ID: 7040679
my thinking is that you can not perform a WHERE ... is null on a logical unmatched return from the outer table.
I'm sure that there's other ways to accomplish this query but if I do I would put my first join result in a temp table then do a WHERE... is null from that table

Create table #temp(
... with all fields from the SELECT

)

INSERT #temp(..fields...)
SELECT
    UserLogin.UserID,
    UserLogin.RegDateTime,
        UserLogin.EmailAddress,
    UserLogin.Password,
        UserLogin.SecretQuestion,
    UserLogin.SecretAnswer,
        UserDetails.Title,
    UserDetails.FirstName,
       UserDetails.Surname    
    FROM
    UserDetails RIGHT OUTER JOIN
    UserLogin ON UserDetails.UserID = UserLogin.UserID

Now some Surname fields in the #temp table will have NULL value. You then can do

SELECT
    UserID,
    RegDateTime,
    EmailAddress,
    Password,
    SecretQuestion,
    SecretAnswer,
    Title,
    FirstName,
    Surname    
FROM #temp
WHERE
    EmailAddress LIKE '%' + 'da'  + '%'
    AND (Surname LIKE  '%' + ''   + '%' Or Surname Is Null)

This solution will make your life easier.
0
 
LVL 9

Expert Comment

by:checoo
ID: 7043650
Hi,

Try the following............

SELECT
    UserLogin.UserID,
    UserLogin.RegDateTime,
        UserLogin.EmailAddress,
    UserLogin.Password,
        UserLogin.SecretQuestion,
    UserLogin.SecretAnswer,
        UserDetails.Title,
    UserDetails.FirstName,
       UserDetails.Surname    
    FROM
    UserDetails RIGHT OUTER JOIN
    UserLogin ON UserDetails.UserID = UserLogin.UserID
GROUP BY
    UserLogin.UserID,
    UserLogin.RegDateTime,
        UserLogin.EmailAddress,
    UserLogin.Password,
        UserLogin.SecretQuestion,
    UserLogin.SecretAnswer,
        UserDetails.Title,
    UserDetails.FirstName,
       UserDetails.Surname
HAVING
    EmailAddress LIKE '%' + 'da'  + '%'
    AND (Surname LIKE  '%' + ''   + '%' )
    AND (Postcode LIKE  '%' + ''  + '%')

I hope this helps.
Cheers
0
 
LVL 19

Author Comment

by:daveamour
ID: 7043691
Sorry for the delay but I've not been able to get onto EE for a couple of days.

I have managed to resolve this problem as if data is searched on which is in table B and there is no record in table B then I don't want to return any results as this will never fulfill the criteria.  So all I needed was to encompass this in a little IF logic.

Thanks everyone for your help.  I want to give the points to someone as you have all been very helpful but I'm not sure who.  Anybody want them?

Cheers

Dave
0
 
LVL 19

Author Comment

by:daveamour
ID: 8426018
Anyone want these points so I can close the question?

Cheers

Dave
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 200 total points
ID: 8427959
I am pretty sure that everybody wants the points :=).

Maybe you could select one randomly ?!
0
 
LVL 19

Author Comment

by:daveamour
ID: 8428043
That sounds like too much work, you can have them!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question