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

Question

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

vijay.sap1255

Mr or Mrs. 500

Points: 592
More actions
July 1, 2016 at 5:57 am

#327914

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

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

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

Anand BI Developer SSCommitted Points: 1772 More actions · Answer 4

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