Getting result set from two tables without null values when using join

  • Dear Experts,

    I was trying to get rid of null values when using full outer join between two tables. For example, I have tables like below:

    Table A Table B

    Eno Ename Eno Ename

    1 A 1 A

    2 B 2 D

    3 C 3 E

    my output should be like below

    A.Eno A.ENAME B.ENAME STATUS

    1 A A SAME

    2 B D DIFF

    3 C E DIFF

    kindly provide your inputs in achieving this.

    Thanks in advance 🙂

    Warm Regards,

    Vj

  • vijay.sap1255 (7/1/2016)


    Dear Experts,

    I was trying to get rid of null values when using full outer join between two tables. For example, I have tables like below:

    Table A Table B

    Eno Ename Eno Ename

    1 A 1 A

    2 B 2 D

    3 C 3 E

    my output should be like below

    A.Eno A.ENAME B.ENAME STATUS

    1 A A SAME

    2 B D DIFF

    3 C E DIFF

    kindly provide your inputs in achieving this.

    Thanks in advance 🙂

    Warm Regards,

    Vj

    Why are you getting NULLs? What code are you using?

    Can you post your sample data this way?

    CREATE TABLE TableA(

    Eno int,

    Ename varchar(10)

    );

    INSERT INTO TableA

    VALUES

    (1, 'A'),

    (2, 'B'),

    (3, 'C');

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • You're getting a NULL because the row doesn't exist in the other table. You'll probably want a different status for that. Try something like this

    Status = CASE

    WHEN TableA.Eno IS NULL THEN 'Doesn''t exist in TableA'

    WHEN TableB.Eno IS NULL THEN 'Doesn''t exist in TableB'

    WHEN TableA.Ename = TableB.Ename THEN 'Same'

    WHEN TableA.Ename <> TableB.Ename THEN 'Different'

    END

    It would have been easier if you'd posted your query (and sample data as requested by Luis) so I didn't have to guess.

    John

  • What column(s) are you using for your Full Outer Join?

    Given the sample data below, it seems obvious that the join should be on the [Eno] column.

    A join on [Eno] only will not produce nulls, but a join that includes [Ename] will.

    Remember, your join is on elements that are alike. You are then comparing other columns that could be different.

    TableA

    1 A

    2 B

    3 C

    Table B

    1 A

    2 D

    3 E

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • HI ,

    U may use this coding Also .

    select

    a.emp_id , a.emp_name name_1 , b.emp_name name_2 ,

    STAT = ( case REPLICATE(a.emp_name,2) when a.emp_name+b.emp_name then 'SAME' else 'DIFF' end)

    from #tab1 a join #tab2 b on a.emp_id = b.emp_id

Viewing 5 posts - 1 through 4 (of 4 total)

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