1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Left join on multiple columns some of which contain nulls

Discussion in 'SQL PL/SQL' started by Sparamanga, Oct 3, 2016.

Thread Status:
Not open for further replies.
  1. Sparamanga

    Sparamanga Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    UK
    Hi,

    I have a query that generates a result set of 5 columns. I need to compare this result set against a table that has the same 5 columns plus 1 more column that has a value for how each row will migrated to another database.

    I need to find the rows from the query which as yet don't have a value assigned in the table.

    I was told to do a left outer join between the query and the table on the matching 5 columns and then set where clause to filter for nulls in the additional column from the right table.

    Hence, this should show the rows not joined from the left table and therefore not having a value assigned in the additional column.

    This does return a result set, but I checked and it does show rows from the query which are actually in the right table.

    It's because some of the columns I'm joining on have nulls.

    How do I get around this?

    I was thinking I could concatenate the columns that potentially contain nulls in both the query and the table and do a left join on the concatenated columns and again filter for nulls in the additional column from the right table.

    Unless there is an easier way to tackle this.

    Any help would be greatly received.

    Thanks,

    Rob.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have already posted this problem in the forum; posting it again doesn't help you and it may cause people to not assist you. You have been asked to provide table create scripts and sample data that looks like what you are working with since the little detail you HAVE provided doesn't help anyone since the output generated isn't what you are seeing.

    This thread will be closed; use the previous thread to avoid confusing others.
     
Thread Status:
Not open for further replies.