Result substitution in query when null (not COALESCE)

19 Aug 2005 6:47 AM

AlexT

Folks

I have a typical Parent / Child table relationship.

I have a field that is always present in Parent but only sometime in
Child (the idea is to override de Parent value for a specific Child
line).

Now I'd like to query Child and, when the said field is null, have
it's value replaced by the value in Parent.

I'm not quite sure that this is possible using a query but a stored
proc might be the trick ? That being said I have no idea how to tackle
this...

Any help appreciated !

--alexT

19 Aug 2005 7:00 AM

Roji. P. Thomas

Why not COALESCE (or ISNULL) ?

SELECT COALESCE(child.property, parent.property) as Something
FROM Child C
INNER JOIN Parent P
ON C.ParentId= P.id

--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com

Show quote

"AlexT" <goo***@atc.ch> wrote in message
news:1124434021.394538.133650@o13g2000cwo.googlegroups.com...
> Folks
>
> I have a typical Parent / Child table relationship.
>
> I have a field that is always present in Parent but only sometime in
> Child (the idea is to override de Parent value for a specific Child
> line).
>
> Now I'd like to query Child and, when the said field is null, have
> it's value replaced by the value in Parent.
>
> I'm not quite sure that this is possible using a query but a stored
> proc might be the trick ? That being said I have no idea how to tackle
> this...
>
> Any help appreciated !
>
> --alexT
>

19 Aug 2005 7:12 AM

AlexT

well, I was under the obviously wrong impression that COALESCE could
only return constants...

Next time I will double check the documentation !

:)

Thanks

--alexT