Re: A SQL statement question that cannot solve by myself

In article <83dl8c$7or$2_at_news.seed.net.tw>,   "fumi" <fumi_at_tpts5.seed.net.tw> wrote:
>
> <laulau823_at_my-deja.com> wrote in message
news:8380ni$rok$1_at_nnrp1.deja.com...
> > Hello all,
> >
> > I have a question related with SQL to query the result from tables,
but
> > still cannot resolve this problem up until now.
> >
> > Following tables:
> > Table test1, Column A, M
> > Table test2, Column A, N
> > Table test3, Column A, B, X
> > Table test4, Column A, B, Y
> > Table test5, Column A, B, Z
> >
> > I want to do outer-join of these tables so I write:
> >
> > select test1.A, test1.M, test2.N, test3.X, test4.Y, test5.Z from
> > test1, test2, test3, test4, test5 where
> > test1.A = test2.A(+) and test1.A = test3.A(+) and
> > test1.A = test4.A(+) and test1.A = test5.A(+)
> > and test3.B = test4.B and test3.B = test5.B and test4.B = test5.B
> > and test3.B in ('C1', 'C2') and test4.B in ('C1', 'C2') and
> > test5.B in ('C1', 'C2')
> >
> > My question is, my SQL can only return the result if test3, test4
and
> > test5 all have the same vaue of B. However, in the real situation,
some
> > B in test3 do not have a value in test4 and vica versa (also in the
case
> > of test3 against test5 and test4 against test5). So in this case,
the
> > above SQL cannot return these records. Could anyone give me hints to
> > rewrite the above SQL such that it can also returns the above
records?
>
> Review your business rules and database design first.
> Why do you need such join conditions?
>
> There are some semantic errors in your statment.
> For example, if "test1.A=test3.A(+)" is one condition,
> "test3.B in ('C1', 'C2')" is another,
> you must rewrite to "test3.B(+) in ('C1', 'C2')".
>
>

I do this join because in the real situation (i.e. my client), the table is logically divided into five tables. In fact, we don't follow the normalization in database design. The fact that the table is divided into five tables is it can reflect the real situation. Table test1 is the main table, test2 to test5 are the detail tables.

However, I need to consider the cases:
1) test1.A = test3.A

   AND test3.B in ('C1', 'C2') only
2) test1.A = test4.A

   AND test4.B in ('C1', 'C2') only
3) test1.A = test5.A

   AND test5.B in ('C1', 'C2') only
4) test1.A = test3.A, test1.A = test4.A.

   AND test3.B, test4.B both in ('C1', 'C2') only 5) test1.A = test3.A, test1.A = test5.A,

   AND test3.B, test5.B both in ('C1', 'C2') only 6) test1.A = test4.A, test1.A = test5.A.

   AND test4.B, test5.B both in ('C1', 'C2') only 7) test1.A match A in test4, test5 and test6.A

   AND test4.B, test5.B, test6.B all in ('C1', 'C2') 8) no test1.A match any A in test4, test5 or test6

The only solution that I found up until now is UNION all the above cases (except case 8). But the SQL is too complicated.

I have tried other posted solution but I got duplicated records or some solutions did not work. Maybe I cannot describe my requirements clearly. Could anyone suggests another solution?

Thanks,
David

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Dec 17 1999 - 11:57:22 CST