Re: How to write this sql urgent...
Date: Mon, 25 Jan 2010 11:54:18 -0800 (PST)
Message-ID: <f3c13a42-8187-4617-9847-bdf6725baf90_at_m25g2000yqc.googlegroups.com>
On Jan 25, 9:28 am, JIACHENG SUN <sun..._at_googlemail.com> wrote:
> 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?- Hide quoted text -
>
> - Show quoted text
Why are you trying to use NVL when you post no NULL values? You also did not post a complete set of sample data so attempting a solution is nearly impossible. Are we to presume by the ... that the data in table 1 supplies all of the values necessary to generate the output you provided? If that is the case the query is MUCH simpler than you'd expect:
select t1.ref, t2.code, t2.description, t2.value from table1 t1 inner join table2 t2 on (t2.code = t1.code) order by 1, 2;
Of course if the data isin't as you describe it the only thing we can do is guess. Please post a representative example of the data and the queries you've written thus far so someone can provide useful guidance.
David Fitzjarrell Received on Mon Jan 25 2010 - 13:54:18 CST