• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

Search On NULL

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
daveamour
Asked:
daveamour
  • 8
  • 3
  • 3
  • +3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
does this help?
...
WHERE
....
AND (b.yourcol LIKE @yourparam OR @yourparam IS NULL)

CHeers
0
 
daveamourAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Indeed, you should post some samples of data and of the expected return, regarding to the parameters...
CHeers
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
daveamourAuthor Commented:
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
 
CarlovskiCommented:
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
 
daveamourAuthor Commented:
Carl, this wont work for the same reason as angelIII's first suggestion.

Thanks

Dave
0
 
daveamourAuthor Commented:
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
 
CarlovskiCommented:
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
 
CarlovskiCommented:
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
 
daveamourAuthor Commented:
The postcode lives in table B
0
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
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
 
nghlanCommented:
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
 
checooCommented:
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
 
daveamourAuthor Commented:
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
 
daveamourAuthor Commented:
Anyone want these points so I can close the question?

Cheers

Dave
0
 
Scott PletcherSenior DBACommented:
I am pretty sure that everybody wants the points :=).

Maybe you could select one randomly ?!
0
 
daveamourAuthor Commented:
That sounds like too much work, you can have them!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 8
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now