Hi folks, Given tables:
Donents entid entname 12 Jeanne Blow 29 Karen & Keith Johnson Entmems entid peoid peotype memstatus 12 12358 P C (Jeanne) 29 12127 P C (Karen) 29 441 P C (Keith) Donates entid dontype 12 deposit 29 deposit Entint entid entinter 12 consumer 29 consumer Why does this query: SELECT donents.theid, donents.entname FROM entmems, donents, donates INTO CURSOR temppks WHERE (entmems.entid = donents.theid AND entmems.peotype == "P" AND entmems.memstatus == "C") AND (donates.entid = donents.theid AND ((UPPER(ALLTRIM(donates.dontype)) == "DEPOSIT"))) AND entint.entid = donents.theid AND (NOT EMPTY(entint.entinter)) AND ((UPPER(ALLTRIM(entint.entinter)) == "CONSUMER")) produce the undesired result: theid entname 29 Karen & Keith Johnson 29 Karen & Keith Johnson 29 Karen & Keith Johnson 29 Karen & Keith Johnson but this query: SELECT donents.theid, donents.entname FROM donents JOIN entmems ON (donents.theid = entmems.entid AND entmems.peotype == "P" AND entmems.memstatus == "C") JOIN donates ON (donates.entid = donents.theid AND ((UPPER(ALLTRIM(donates.dontype)) == "DEPOSIT"))) JOIN entint ON (entint.entid = donents.theid AND (NOT EMPTY(entint.entinter)) AND ((UPPER(ALLTRIM(entint.entinter)) == "CONSUMER"))) INTO CURSOR temppks produces the desired result: 12 Jeanne Blow 29 Karen & Keith Johnson 29 Karen & Keith Johnson 29 Karen & Keith Johnson 29 Karen & Keith Johnson In other words, why do I get what I want from using JOINs but don't using a WHERE clause? I need to use both in different circumstances and I need them both to return the same results. Can anyone tell me what I'm doing wrong? Thanks! Ken Dibble www.stic-cil.org _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
