April 21, 2011 at 12:24 am
Hi,
I want query to join the table -1 and table -2 for getting the output like table -3
table -1
A X
1 7
2 8
3 9
table -2
B Y
2 9
3 6
4 7
Table -3
Z X Y
1 7 null
2 8 9
3 9 6
4 null 7
If i used the full outer join :
select a.eno,a.no,b.no
from ep1 a full outer join ep2 b
on a.eno=b.eno
i can get like this :
eno no no
----------- ----------- -----------
2 7 9
3 8 8
NULL NULL 6
1 6 NULL
But i want get value in the eno column instead of null
Actually my need is for joining three tables. Kindly looking for suggestion
Regards
Vijayakumar N
April 21, 2011 at 12:33 am
April 21, 2011 at 1:08 am
I think this would be perfect:
select isnull(x.eno,c.eno),x.ano,x.bno,c.no
from (select isnull(a.eno,b.eno) 'eno',a.no 'ano', b.no 'bno'
from ep1 a full outer join ep2 b
on (a.eno=b.eno)) x full outer join ep3 c
on (x.eno=c.eno)
order by 1
Regards
Vijayakumar N
April 21, 2011 at 1:26 am
Regarding the solution you added to your original query:
select ISNULL(a.eno,b.eno) as eno,a.no,b.no
from ep1 a full outer join ep2 b
on a.eno=b.eno
should return the expected output.
Another option would be a table holding all eno values and use left join to connect the three tables.
As a side note: I, personally, would vote against using ORDER BY 1. Use a column name or alias instead.
April 21, 2011 at 2:16 am
To answer your question in a PM that you've...
heard that the usage of the column number is the perfect choice instead of using alias name or column name in the SQL 2008. Particularly in SQL 2008, the ambigious column error arise for this case.
The issue for the ambiguous is the alias you're using: it's identical to the column names of some of your tables.
Alternative one: assign a unique alias (might be not really an option...)
Alternative two: use ORDER BY isnull(x.eno,c.eno)
I don't know where that rumor originated, but it's simply not true. As a matter of fact, if it becomes a habit and is used in a query against a single table, then it may return unexpected results if someone decides to change the order of columns for that table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply