Re: sql question

From: Guenter Gersdorf <G.Gersdorf_at_tu-bs.de>
Date: 1995/11/30
Message-ID: <49kkp0$qp7_at_ra.ibr.cs.tu-bs.de>#1/1

In article <49j5a7$plk_at_hpscit.sc.hp.com>, sunyi_at_PROBLEM_WITH_INEWS_GATEWAY_FILE says...
>
>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.
>
This is perfectly right,
looking only at

        where T001.col1=T002.col1 and T002.col4=T003.col4 which defines the crossproduct (not a join!!) delivers 4 rows: row 1 of t1 with row 1 of t2 with row 1 of t3 row 1 of t1 with row 1 of t2 with row 2 of t3 row 2 of t1 with row 2 of t2 with row 1 of t3 row 2 of t1 with row 2 of t2 with row 2 of t3 the condition T003.col8=1 selects the 1. and 3. of these.

Guenter

---

Guenter Gersdorf                         Phone:      +49/(0)531/391-7634
Inst. f. Werkzeugmaschinen               Fax:                      -5842
und Fertigungstechnik, TU Braunschweig   E-Mail:     G.Gersdorf_at_tu-bs.de

Langer Kamp 19b, D-38106 Braunschweig http://www.iwf.ing.tu-bs.de/~gg Received on Thu Nov 30 1995 - 00:00:00 CET