news:1673c1fd.0301251110.7454523_at_posting.google.com:
> Suppose the relationship as
>
> table t1,t2,t3,t4
>
> t1(ID1 integer,ID2 char) fk ID1 -> t2,fk ID2 ->t4
> t2(ID1 integer,category char) fk category ->t3
> t3(category char,beer double,pork double)
> t4(ID2 char,discount double,total double)
>
> what listing from t1'data as
>
> ID1 beer pork ID2 discount total
> .....
> .....
>
>
> Can any one tell me how to use pure sql without creating any
> function/view/trigger... to accomplish it?
>
> I've tried
> select t1.ID1,t3.beer,t3.pork,t1.ID2,t4.discount,t4.total
> from t1,t2,t3,t4
> where
> t1.id1=t2.id1 and
> t2.category=t3.category and
> t1.id2=t4.id2
>
> but it listed lots and lots duplicated rows.
>
> Thanks lots in advance.
>
> --
> John
> Toronto
>
One rule of thumb for writing efficient (& correct) SQL
is that if a table is NOT included in the SELECT clause
then the table should NOT exist within the FROM clause.
Specifically in your case, you are NOT selecting any
column from table "t2"; therefore it should not be
in the FROM clause. I suspect that this is why you
are getting "lots and lots duplicated rows".
HTH & YMMV
