Re: sql question

sunyi_at_PROBLEM_WITH_INEWS_GATEWAY_FILE (Tropical Sun) wrote:
>one funny table join problem, however i don't know the reason.
>there are 3 tables defined like(in which the colxx is the column name)
>
> t1 t2 t3
>************** ************************* ********************************
>col1 col2 col3 col4 col5 col1 col6 col7 col4 col5 col8 col9 col10 col11
>************** ************************* *********************************
>
>1 0 b-1 1 1 1 c-1 c-2 1 1 1 c-7 6 c
>2 0 b-2 1 2 2 c-3 c-4 1 2 3 c-8 NULL NULL
>3 1 b-3 2 3 3 c-5 c-6 2 3 3 c-9 NULL NULL
>
>
>pls notice that t1.col1 appears in t2.col1 and t2.col4 appears in t3.col4
>
>i expect the following statement:
>select distinct T001.col1, T001.col2, T003.col3, T003.col8 from t1 T001, t2 T002, t3 T003 where T001.col1=T002.col1 and T002.col4=T003.col4 and T003.col8=1
>can give the result:
> 1 0 b-1 1
>
>however, in fact it gives me the following:
>1 0 b-1 1
>2 0 b-2 1
>
>any explaination of the result is appreciated.
>
>i'm running ORACLE 7.1.3 on HP 9000/887(HP-UX 9.04)
>
>
>thanks in advance
>
>
>SunYi
>sunyi_at_hpsgns1.sgp.hp.com

Your statement should generate an error since I don't see any col3 in t3. As for the result,

t3.col8=1 returns 1 row
but t2.col4=t3.col4 (=1) will return 2 rows and not 1 as you are expecting.
These 2 rows when joined with t1 would produce 2 rows and that's what you get.

Hope it explains your problem.
Regards,
Suman. Received on Thu Nov 30 1995 - 00:00:00 CET