Re: sql question

Tropical Sun (sunyi_at_PROBLEM_WITH_INEWS_GATEWAY_FILE) 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

Hi SunYi,

Your problem is simply that there is a one to many relationship between table two and table three. In other words, you need to restrict what rows are being selected in table two also.

Hope this helps,
rick   Received on Thu Nov 30 1995 - 00:00:00 CET