Left join query

[neskin]

hi ,
What I am doing wrong? In my script I have to subqueries first return rownum from 65 to 100 i want to return records with Lmax from 65 to 100 even they don't exist in secod subquery
rmtid Lmax ne
1 65 0
1 66 0
1 67 5
2 65 20
2 66 15
...

select rmtid,a.Lmax,b.Lmax,ne
from
(select Lmax
from (select rownum Lmax from all_objects
       where rownum <= 100)
where Lmax >=65)a left outer join 	   
	   (select rmtid,(round(maxlevel)) Lmax,count(round(maxlevel))ne from events
	   where TO_NUMBER(TO_CHAR(startdate,'mm'))='07' AND 	  TO_NUMBER(TO_CHAR(startdate,'yyyy'))='2005' 
					  group by rmtid,round(maxlevel))b
					 on a.Lmax  = b.Lmax 
					  order by b.rmtid,a.Lmax

 

[SantaMufasa]

Neskin,

The first thing that you are doing wrong is asking what you are doing wrong without posting the Oracle error message that you are receiving.

You are posting in the "Oracle 8..." forum and I don't remember with which version Oracle began support for the "left outer join..." syntax and I don't know the version of Oracle that you are using, but instead of using that syntax, I, personally believe that it's easier to use Oracle's "(+)" left-outer-join operator:

select rmtid,a.Lmax,b.Lmax,ne
from (select Lmax
        from (select rownum Lmax from all_objects
               where rownum <= 100)
       where Lmax >=65) a
    ,(select rmtid,round(maxlevel) Lmax,count(maxlevel)ne
        from events
       where TO_CHAR(startdate,'yyyymm')='200507'
       group by rmtid,round(maxlevel)) b
 where a.Lmax  = b.Lmax(+)
 order by b.rmtid,a.Lmax
/

I also "tightened" your code a little by eliminating some extraneous code.

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[neskin]

Musafa ,
I am using Oracle9i.
This script which you corrected does not give me the results what I want .
it is still not showing for rmtid 1 Lmax 65
Current result is
rmtid Lmax ne
1 71 10
1 72 20
2 65 12
2 66 14
2 67 15
...
I need
1 65 0
1 66 0
1 67 0
....
1 71 10
1 72 20
.....

Thanks in advance

 

[SantaMufasa]

Neskin,

Can you please post the results of this query:

SELECT rmtid, COUNT(*)
  from EVENTS
 where TO_CHAR(startdate,'yyyymm')='200507'
   and lmax between 65 and 100
 group by rmtid;

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[neskin]

I have slightly modified the query
SELECT rmtid, COUNT(*)
from EVENTS
where TO_CHAR(startdate,'yyyymm')='200507'
and round(maxlevel) between 65 and 100
group by rmtid

the results of this query:

1 3376
2 3562
3 4113
4 4631
5 5935
6 5342
7 234
8 6371
9 1101
10 1553
11 557
12 6433
13 684
14 2059
15 5678
16 4822
17 5573
18 7031
20 2239
21 5051
22 6345
23 1879
24 2712
25 614
26 5382
27 1197
28 254
905 3067
907 1447
908 1449