[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

fill in outer join?

Mark B

3/29/2007 9:00:00 PM

I have two tables link via a right outer join. In the results where
there is no record in the "B" table, it returns <null>. Is there a
way, via maybe a case statement, that I can put something into that
field?

Thanks,

Mark
2 Answers

David Portas

3/29/2007 9:11:00 PM

0

On 29 Mar, 21:59, "Mark B" <mark_b...@yahoo.com> wrote:
> I have two tables link via a right outer join. In the results where
> there is no record in the "B" table, it returns <null>. Is there a
> way, via maybe a case statement, that I can put something into that
> field?
>

Use COALESCE. Example below.

I'm usually averse to using RIGHT outer join. LEFT does just as well,
whereas RIGHT is much less common and sometimes causes people to do a
double-take when they encounter it in someone else's code (it has that
effect on me occasionally anyway). Not important but I just thought
I'd mention it.

SELECT
A.col1,
COALESCE(B.col2, 'Something else') AS col2
FROM A
LEFT JOIN B
ON A.col1 = B.col1;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
--

Mark B

3/30/2007 2:42:00 PM

0

On Mar 29, 4:11 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:
> On 29 Mar, 21:59, "Mark B" <mark_b...@yahoo.com> wrote:
>
> > I have two tables link via a right outerjoin. In the results where
> > there is no record in the "B" table, it returns <null>. Is there a
> > way, via maybe a case statement, that I can put something into that
> > field?
>
> Use COALESCE. Example below.
>
> I'm usually averse to using RIGHT outerjoin. LEFT does just as well,
> whereas RIGHT is much less common and sometimes causes people to do a
> double-take when they encounter it in someone else's code (it has that
> effect on me occasionally anyway). Not important but I just thought
> I'd mention it.
>
> SELECT
> A.col1,
> COALESCE(B.col2, 'Something else') AS col2
> FROM A
> LEFTJOINB
> ON A.col1 = B.col1;
>
> --
> David Portas,SQLServer MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version ofSQLServer you are using and specify the content
> of any error messages.
>
> SQLServer Books Online:http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
> --

Perfect! Thanks so much. (I also rewrote the procedure as a left
join).

Mark B