Re: Strange results

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 1 Nov 2006 08:07:49 -0800
Message-ID: <1162397269.291590.70450@i42g2000cwa.googlegroups.com>

tawright915 wrote:
> Okay here is the setup:
> Building table has ky_bldg_no, city_cd, address_city
> Premise table has ky_prem_no and ky_bldg_no
> Bill table has ky_prem_no, ky_acct_no, cd_ba_stat
>
> I need to find all ky_acct_no that reside in a particular city/county,
> which is found in the building table (city_cd and address_city).
> So here is the query I wrote:
> 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 ;
>
> The problem is that I am getting duplicate records when running.
> Do I have something setup wrong?
>
> Thanks
> Tom

If we format the SQL statement like this, do you see the source of the 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;

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Nov 01 2006 - 10:07:49 CST