Re: Strange results

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 1 Nov 2006 09:08:54 -0800
Message-ID: <1162400934.274332.172780@b28g2000cwb.googlegroups.com>

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.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Nov 01 2006 - 11:08:54 CST