 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
nix
Guest
|
 Posted: Wed Dec 10, 2003 12:23 am Post subject: MySQL - Unknown table 'clientdetails' in where clause |
 |
|
Hi, I want run a select statement that retrieves information, based on a few
requirements...
============================
select orderdetails.*, clientdetails.ClientStatus
from orderdetails LEFT JOIN clientdetails ON (orderdetails.ClientID =
clientdetails.ClientID)
where LastBilled < 37956
AND BillingCommencement < 37965.08
AND (Product_FIRSTGEAR = '0' OR Product_SECONDGEAR = '0' OR
Product_THIRDGEAR = '0' OR Product_FOURTHGEAR = '0') AND (OrderStatus
='ACTIVATED')
============================
The above statement works fine, however, when I add the condition of another
table......
==============================
select orderdetails.*, clientdetails.ClientStatus
from orderdetails LEFT JOIN clientdetails ON (orderdetails.ClientID =
clientdetails.ClientID)
where LastBilled < 37956
AND BillingCommencement < 37965.08
AND (Product_FIRSTGEAR = '0' OR Product_SECONDGEAR = '0' OR
Product_THIRDGEAR = '0' OR Product_FOURTHGEAR = '0') AND (OrderStatus
='ACTIVATED' AND clientdetails.ClientStatus = 'APPROVED FOR PAYMENTS')
==============================
it gives me the error " Unkown table 'clientdetails' in where clause". I am
using D7 Pro, MySQL on Win XP Pro SP2... what am I doing wrong?????
Thanx in adv.
Karl
|
|
| Back to top |
|
 |
Dell Stinnett
Guest
|
| Posted: Wed Dec 10, 2003 2:47 pm Post subject: Re: MySQL - Unknown table 'clientdetails' in where clause |
|
|
Why are you using a Left Join? The Left Join means that you'll get all
records from OrderDetails regardless of whether there's a corresponding
record in ClientDetails. By setting a where condition that includes the
ClientDetails table, your Left Join isn't going to work right. I would do
something like this:
select orderdetails.*, clientdetails.ClientStatus
from orderdetails,
clientdetails
where (orderdetails.ClientID = clientdetails.ClientID)
AND LastBilled < 37956
AND BillingCommencement < 37965.08
AND (Product_FIRSTGEAR = '0' OR Product_SECONDGEAR = '0' OR
Product_THIRDGEAR = '0' OR Product_FOURTHGEAR = '0') AND (OrderStatus
='ACTIVATED')
-Dell
|
|
| Back to top |
|
|
baloonatic@hotmail.com
Guest
|
| Posted: Wed Dec 10, 2003 7:33 pm Post subject: Re: MySQL - Unknown table 'clientdetails' in where clause |
|
|
On Wed, 10 Dec 2003 02:23:03 +0200, "nix" <nimand (AT) nix (DOT) com> wrote:
| Quote: | Select * From
(
select orderdetails.*, clientdetails.ClientStatus
from orderdetails LEFT JOIN clientdetails ON (orderdetails.ClientID =
clientdetails.ClientID)
where LastBilled < 37956
AND BillingCommencement < 37965.08
AND (Product_FIRSTGEAR = '0' OR Product_SECONDGEAR = '0' OR
Product_THIRDGEAR = '0' OR Product_FOURTHGEAR = '0') AND (OrderStatus
='ACTIVATED')
) asuitablealias WHERE asuitablealias.ClientStatus = 'APPROVED FOR |
PAYMENTS'
might achieve the desired effect. From the error it's jibbing because
clientdetails is used in the where clause but not in the from clause
as it only appears in the join syntax.
Bit of a pain though is n't it.
Come back SQL Server all is relative though of course not free
|
|
| Back to top |
|
|
nix
Guest
|
| Posted: Thu Dec 11, 2003 8:32 am Post subject: Re: MySQL - Unknown table 'clientdetails' in where clause |
|
|
Thanx I'll give it a try...
Karl
"Dell Stinnett" <dell.stinnett> wrote
| Quote: | Why are you using a Left Join? The Left Join means that you'll get all
records from OrderDetails regardless of whether there's a corresponding
record in ClientDetails. By setting a where condition that includes the
ClientDetails table, your Left Join isn't going to work right. I would do
something like this:
select orderdetails.*, clientdetails.ClientStatus
from orderdetails,
clientdetails
where (orderdetails.ClientID = clientdetails.ClientID)
AND LastBilled < 37956
AND BillingCommencement < 37965.08
AND (Product_FIRSTGEAR = '0' OR Product_SECONDGEAR = '0' OR
Product_THIRDGEAR = '0' OR Product_FOURTHGEAR = '0') AND (OrderStatus
='ACTIVATED')
-Dell
|
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
