Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
superhero
Yak Posting Veteran
52 Posts |
 Posted - 2007-07-25 : 04:44:33
|
| HiIf I'm doing a simple left outer join as in:select * from tableA left outer join tableB on tablea.userid = tableb.useridHow can I make it show "NO PARENT" instead of NULL for rows without relational data in tableB?I have millions of SQL books and I know I read somewhere a while back how to do it, but I can't remember exactly how!Thanks |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-07-25 : 04:47:28
|
use CASE WHEN . . . or isnull or coalescecase when tableb.userid is null then 'NO PARENT' else tableb.userid endorisnull(tableb.userid, 'NO PARNET')orcoalesce(tableb.userid, 'NO PARNET')
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
superhero
Yak Posting Veteran
52 Posts |
Posted - 2007-07-25 : 05:01:10
|
| I used isnull(tableb.userid, 'NO PARNET') - that's the one I saw in one of my books.You're a lifesaver. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-07-25 : 05:04:44
|
quote:
I have millions of SQL books
Wow ! you really read them all ? 
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-25 : 05:50:30
|
and in the end all you need is just one: Books On-line  _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
|
|
|
Left outer join replace NULL with something else