Post reply

where one field is NULL

  • February 7, 2007 at 3:40 am

    #176761

    Hello,

    Easy Q for everyone (I should know this :blush

    If you are doing for example

    WHERE a.surname <> b.surname

    and you have

    Smith        (Null field)

    My query isnt bringing this back because one side is NULL. How do you set up a not equal to query where you are sure that you may have some NULLs somewhere and want these to be returned too?

    Thanks

    Debbie

     

     

  • February 7, 2007 at 4:10 am

    #688237

    Use a outer join

    something like

    from table a

    left outer join table b ON a.surname = b.surname

    in this case irrespective of whethere there is a matching record for a record of table a in table b it will return the result.

    the same is applicable with right outer join where data in table b takes precedence and the number of records returned will be equal to the rows in table b.

     

     

    Prasad Bhogadi
    www.inforaise.com

  • February 7, 2007 at 4:13 am

    #688239

    Also make sure you do not use WHERE condition. Use AND condition if you are checking more than one condition after the OUTER JOIN. If you are including any condition that you want to filter on then you can use WHERE condition which acts as a INNER JOIN.

     

    Prasad Bhogadi
    www.inforaise.com

  • February 7, 2007 at 4:16 am

    #688240

    Thanks for that. I was hoping to not go for an outer join though in this case. Maybe a conversion or something in the where clause to convert all Nulls or empty strings to something.........

    When I have something I can use Ive got to go back and amend ALOT of queries and Im hoping I only need to amend that specific WHERE clause.

    Debbie

     

  • February 7, 2007 at 4:25 am

    #688244

    Ok, can you post one of your queries, so that I can what exactly can be done. You can use CASE in your where clause like

    WHERE

     CASE WHEN ISNULL(b.surname,'') =''

    THEN

       a.Surname

    ELSE

    b.surname END)=a.surname

    I am not sure if I got your problem right but just gave it a try.

     

     

     

    Prasad Bhogadi
    www.inforaise.com

  • February 7, 2007 at 4:43 am

    #688249

    Thanks for that,

    I tried the above but didnt get any results back.

    SELECT ID.MNAME, sd.*

    FROM dbo.Import_FULL sd INNER JOIN #ATTENDANCES_a ID

    ON  sd.Forename = ID.FNAME AND sd.DateOfBirth = ID.DOB AND sd.Surname = ID.SNAME

    AND sd.DFES = ID.DFEE

    WHERE sd.UPN <> ID.UPN

    AND sd.UPN NOT LIKE '%[a-z]'

    --AND sd.Middlename <> I.MNAME

    ORDER BY sd.Surname, sd.Forename

    GO

    With the AND sd.Middlename <> I.MNAME commented out you get the following example

     B846463446ElanaSmithNULL
    JohnK845664646AshleyYatesNULL
     M8463757646HarryWhiteNULL

    With the script in you get no records and even if you exchange <> for = you get no records. I think case may work around each middlename but again Im not to sure how this would work.

    Thanks for your help on this one

  • February 7, 2007 at 5:27 am

    #688255

    SELECT

    ID.MNAME, sd.*

    FROM

    dbo.Import_FULL sd INNER JOIN #ATTENDANCES_a ID

    ON

    sd.Forename = ID.FNAME AND sd.DateOfBirth = ID.DOB AND sd.Surname = ID.SNAME

    AND

    sd.DFES = ID.DFEE

    WHERE

    sd.UPN <> ID.UPN

    AND

    sd.UPN NOT LIKE '%[a-z]'

    AND

    CASE WHEN ISNULL(sd.Middlename,'') = ''

    THEN

    ISNULL(ID.MNAME,'')

    ELSE

    sd

    .Middlename

    END) = ISNULL(ID.MNAME,'')

    ORDER

    BY sd.Surname, sd.Forename

    Prasad Bhogadi
    www.inforaise.com

  • February 7, 2007 at 5:40 am

    #688259

    Just tried it and it works which is great because now I can exchange my <> clause in all my tables where I may have problems because of NULL fields.

    Just have to get my head round how it works now.

    Thanks again

    Debbie

     

Viewing 8 posts - 1 through 7 (of 7 total)

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