Re: How to list parent's table's attribute via sql without function or trigger?

Ban Spam <ban-spam_at_operamail.com> wrote in message news:<Xns930E74742F6D1SunnySD_at_68.6.19.6>...
> johnwu_at_yorku.ca (John) wrote in
> 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
>
> HAND!
Thanks lots, I got it now.

John Received on Sun Jan 26 2003 - 00:04:56 CET