on this one.
Is there anyone on this list who lives in London (the UK that is, not in
CA or USA)?
Cheers,
Stuart
-----Original Message-----
From: Deanna Schneider [mailto:[EMAIL PROTECTED]
Sent: 10 February 2004 12:45
To: CF-Talk
Subject: Re: Complex Database Stuff
> <cfquery name = "GetMyEnqiryList" datasource = "020">
> SELECT e.nquiryID, e.EnquiryMobileNumber, e.EnquiryDateTime,
> p.ProductCodeID. p.ProductCodeDescription
> FROM EnquiryTable e, ProductCodeTable p
> WHERE e.ProductCodeID in (SELECT ProductCodeID
> FROM ProductCodeTable
> WHERE CompanyOwnerID=#client.companyownerID#)
> </cfquery>
This won't work accurately - you'll get a cartesian product because you
haven't joined the enquirytable and the productcodetable.
> <cfquery name = "GetMyEnqiryList" datasource = "020">
> SELECT e.EnquiryID, e.EnquiryMobileNumber, e.EnquiryDateTime,
> e.ProductCodeID, p.ProductCodeDescription
> FROM EnquiryTable e JOIN ProductCodeTable p on
> (e.ProductCodeID=p.ProductCodeID)
> WHERE p.CompanyOwnerID=#client.companyownerID#
> </cfquery>
This is essentially accurate and will perform better than the subquery,
as
well. Access syntax always trips me up, but you may need it to be like
below
(note that I also added the cfqueryparam):
SELECT e.EnquiryID, e.EnquiryMobileNumber, e.EnquiryDateTime,
e.ProductCodeID, p.ProductCodeDescription
FROM EnquiryTable e INNER JOIN ProductCodeTable p on
e.ProductCodeID=p.ProductCodeID
WHERE p.CompanyOwnerID=<cfqueryparam cfsqltype-"cf_sql_integer"
value="#client.companyownerID#">
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
