Re: Join Table

On 28 Sep 1998 08:22:44 GMT, paulkcng_at_news.netvigator.com (Ng K C Paul) wrote:

>No, for your join in your select statement, only one row would be returned.
>What I want is the join that returns 5 rows as using the key from tab1
>and tab2 but the keys are different value. I want to know if it
>is possible to join tables with different key values as indicated in my
>sample data.
>: >
>: >tab1_key tab2_key
>: >-------- --------
>: >10 9
>: >20 18
>: >30 27
>: >40 39
>: >50 50
>: >

Maybe this is what you want:
select tab1_key, tab2_key from
 (select r1.tab1_key mx, max(r3.tab1_key) mi   from tab1 r1, tab1 r3
   where r3.tab1_key < r1.tab1_key
   group by r1.tab1_key
  UNION select min(tab1_key), 0 from tab1 ) t1,    tab1, tab2
 where t1.mi < tab2_key and t1.mx >= tab2_key    and t1.mx = tab1_key;
This will join tab2 to tab1 such as tab2_keys will join to the tab1_key that is the smallest one greater or equal to tab2_key. I guess the performance will be horrible.

-- 
Regards,
Roland