Solved

Search On NULL

Posted on 2002-05-28
18
292 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
  • 8
  • 3
  • 3
  • +3
18 Comments
 
LVL 142

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 142

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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 19

Author Comment

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

Expert Comment

by:ScottPletcher
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:ScottPletcher
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:
ScottPletcher earned 50 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now