unfortunatly I still do. In theory I agree with you, however this is
not the case when I run this query.
Tom
Serge Rielau wrote:
> tawright915 wrote:
> > Thank you so much.....I see what you are talking about.
> >
> > Once again you have been very helpful.
> >
> > Thanks
> > Tom
> >
> > Charles Hooper wrote:
> >> tawright915 wrote:
> >>> Charles,
> >>> I do not see it. If I strip out my qualifiers, I'm joining building to
> >>> premise then bill_acct to premise.
> >>> Isn't a Cartesian join where you join one table to another where there
> >>> is no key the same between them?
> >>>
> >>> Thanks
> >>> Tom
> >>>
> >>> Oh and for the correct code it's
> >>> Select distinct a.AD_SERV_CITY,
> >>> a.cd_city_cnty,
> >>> d.ky_ba,
> >>> d.ky_prem_no
> >>> from building a,
> >>> premise c,
> >>> bill_acct d
> >>> where
> >>> ((a.cd_city_cnty in ('0627')
> >>> and a.KY_BLDG_NO = c.KY_BLDG_NO
> >>> and c.ky_prem_no = d.ky_prem_no
> >>> and d.cd_ba_stat = '02')
> >>> or (upper(a.AD_SERV_CITY) like '%BYRNES
> >>> MILL%'))
> >>> order by 1, 3, 4 ;
> >> Cartesian join?
> >> SELECT DISTINCT
> >> A.AD_SERV_CITY,
> >> A.CD_CITY_CNTY,
> >> D.KY_BA,
> >> D.KY_PREM_NO,
> >> FROM
> >> BUILDING A,
> >> PREMISE C,
> >> BILL_ACCT D,
> >> WHERE
> >> (
> >> (
> >> A.CD_CITY_CNTY IN ('0627')
> >> AND A.KY_BLDG_NO = C.KY_BLDG_NO
> >> AND C.KY_PREM_NO = D.KY_PREM_NO
> >> AND D.CD_BA_STAT = '02')
> >> OR (
> >> UPPER(A.AD_SERV_CITY) LIKE '%BYRNES MILL%')
> >> )
> >> ORDER BY
> >> 1,
> >> 3,
> >> 4;
> >>
> >> Look closely at the ( ) and the placement of the OR. The SQL statement
> >> as written requires that if UPPER(A.AD_SERV_CITY) LIKE '%BYRNES MILL%'
> >> then it is OK to have a Cartesian join between the three tables. If
> >> UPPER(A.AD_SERV_CITY) is not LIKE '%BYRNES MILL%', then the three
> >> tables are being joined together properly. You may need to relocate
> >> the ( ) and the OR - this is not easy to see with the way your SQL
> >> statement was formatted.
> Are you getting duplicate records without or with DISTINCT?
> The cartesian product explaine dups if you ommit DISTINCT.
> But the query as posted should not return any dups no matter what is in
> the FROM or WHERE clauses the way I see it.
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
>
> WAIUG Conference
> http://www.iiug.org/waiug/present/Forum2006/Forum2006.html
Received on Wed Nov 01 2006 - 12:28:25 CST
