You should be aiming for something like this.
<cfquery name="" datasource="">
SELECT DISTINCT mc.ClientName, AliasName, AlphaPrefix, SiteID, Infinity,
AuthCode, POBox, POBoxCombo, mc.Comments, DID, SVCType, mc.AcctStatus,
PreparedDate, URL, TerminationReason, TerminationDesc, billingaddr,
billingaddr2, billingstate, billingcity, billingzip, billingcontact,
billingemail, billingphone, billingext, billingfaxnumber, billingcellnumber,
legalname
FROM tblMasterClients mc
JOIN tbllegaladdr la ON (mc.clientname = la.legalname)
JOIN tblbillingaddr ba ON (you related fields)
where mc.acctstatus = 'A'
OR mc.acctstatus = 'ACT'
ORDER BY tblmasterclients.clientname
</cfquery>
I would usually include the table alias in front of every selected field as
well to save on problems later and to ease debugging. (i.e.: mc.AliasName
instead of just AliasName)
--
Jay
> -----Original Message-----
> From: hammerin hankster [mailto:[EMAIL PROTECTED]
> Sent: 06 July 2004 15:51
> To: CF-Talk
> Subject: Need Serious Help - query broken
>
> I've got the following query that hangs the MX ODBC service
> (at least I attempt to stop it after 20 minutes).
>
> <CFQUERY NAME="readesi" DATASOURCE="esidb1">
> SELECT DISTINCT tblmasterclients.ClientName,
> AliasName, AlphaPrefix, SiteID, Infinity, AuthCode, POBox,
> POBoxCombo, tblmasterclients.Comments, DID,
> SVCType, tblmasterclients.AcctStatus, PreparedDate, URL,
> TerminationReason, TerminationDesc, billingaddr,
> billingaddr2, billingstate, billingcity, billingzip,
> billingcontact, billingemail, billingphone, billingext,
> billingfaxnumber, billingcellnumber, legalname
>
> FROM tblMasterClients, tblbillingaddr, tbllegaladdr
>
> where tblmasterclients.clientname = tbllegaladdr.legalname
>
> and tblmasterclients.acctstatus = 'A'
> OR tblmasterclients.acctstatus = 'ACT'
>
>
>
> ORDER BY tblmasterclients.clientname
>
> The query seems simple enough but what's not obvious is the
> relationships between some of the tables. If I posted a
> screen cap of the relationship, any chance I could get some help?
>
> Thanks!
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
