Re: How to write this sql urgent...

From: JIACHENG SUN <sunjcb_at_googlemail.com>
Date: Mon, 25 Jan 2010 06:28:41 -0800 (PST)
Message-ID: <9397bb02-234c-4f7a-9922-be2fc5aaea12_at_h2g2000yqj.googlegroups.com>

On Jan 25, 2:23 pm, JIACHENG SUN <sun..._at_googlemail.com> wrote:
> table 1
> ref     code
> 1       code_1
> 1       code_2
> 2       code_3
> 2       code_5
> .......
> .......
>
> table 2
> code      code_description    value
> code_1    description1         3.4
> code_2    description2         3.5
> code_3    description3         0
> code_4    description4         0
> code_5    description5         0
>
> how to write a sql to get the result below:
>
> ref    code       description      value
> 1      code_1    description1       3.4
> 1      code_2    description2       3.5
> 1      code_3    description3       0
> 1      code_4    description4       0
> 1      code_5    description5       0
> 2      code_1    description1       3.4
> 2      code_2    description2       3.5
> 2      code_3    description3       0
> 2      code_4    description4       0
> 2      code_5    description5       0
> ...........
> ............
>
> thanks so much!!

I tried to use outer join and nvl function, but still couldn't get the result i need. Cos if we have 1000 refs in table 1, it is impossible to use nvl. how do your guys think? Received on Mon Jan 25 2010 - 08:28:41 CST