>
> <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'
>
Looks like you have 3 tables there and only 2 of them are joined. At a
guess I would say you need another join between either tblmasterclients
and tblbillingaddr, for example.
FROM tblMasterClients
INNER JOIN tblbillingaddr ON tblMasterClient.ClientID =
tblbillingaddr.ClientID
INNER JOIN tbllegaladdr ON tblMasterClient.ClientID =
tbllegaladdr.ClientID
WHERE tblmasterclients.acctstatus = 'A' OR tblmasterclients.acctstatus
= 'ACT'
I would suggest that you ensure you have unique client identifiers as
foriegn keys in your legal and billing address tables, as these will
provide a much better relationship and a much speedier means of querying
your database than comparing/linking using, probably unindexed, client
name text fields.
Hope this helps
Regards
Stephen
PS. you may need LEFT joins rather than INNER joins if your clients
don't have to have legal and/or billing addresses.
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
