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:17:34 CST
