weeks and kept telling myself to come back to it later. I just borrowed
my friend's SAMS Teach Yourself SQL in 10 minutes and saw that subquery
stuff in there but didn't read up on it as I thought it was just a SQL
thing and not access as I'd never seen it before.
Thanks again.
Stuart
-----Original Message-----
From: Brook Davies [mailto:[EMAIL PROTECTED]
Sent: 10 February 2004 00:08
To: CF-Talk
Subject: Re: Complex Database Stuff
This query will return only enquiries for products that match the
companyownerID by using a subquery.
<cfquery name = "GetMyEnqiryList" datasource = "020">
SELECT EnquiryID, EnquiryMobileNumber, EnquiryDateTime, ProductCodeID
FROM EnquiryTable
WHERE ProductCodeID in (SELECT ProductCodeID
FROM ProductCodeTable
WHERECompanyOwnerID=#client.companyownerID#)
</cfquery>
Brook
At 03:46 PM 2/9/2004, you wrote:
>Hi guys,
>
>
>Well it's complex to me anyway.
>
>
>I have 3 tables. 1 is the EnquiryTable (SMS enquries), the other is
the
>ProductCodeTable (products) and third is the CompanyTable (company
which
>owns product).
>
>
>An enquiry comes via SMS including a ProductCodeID. on receiving the
>enquiry, I am inserting this enquiry into EnquiryTable, inserting
fields
>EnquiryID (primary, auto-number), EnquiryMobileNumber, EnquiryDateTime
>and ProductCodeID.
>
>
>I am then referencing the ProductCodeID which is in the
ProductCodeTable
>and messaging back the customer with the product details.
>
>
>Further down the road, a CompanyOwner can come along and view all
>his/her enquiries. But because when I first inserted the enquiries
into
>EnquiryTable I didn't insert who the CompanyOwner (of CompanyTable)
was.
>So. when the CompanyOwner logs into the system I create a
>client.companyowner so I know who he is. but then when he wants to list
>all his enquiries (from mobile phones) it gets confusing. How do I
>query the EnquiryTable to pull only his product enquiries and not
>everybody else's?
>
>
><cfquery name = "GetMyEnqiryList" datasource = "020">
>SELECT EnquiryID, EnquiryMobileNumber, EnquiryDateTime, ProductCodeID
>FROM EnquiryTable, ProductCodeTable
></cfquery>
>
>
>I could have inserted the CompanyOwner to the EnquiryTable initially
>when I first received the enquiry but I am a little worried that
pulling
>more info and writing more info could slow down the process of getting
>the customer his/her info via SMS.
>
>
>Anyone understand what the hell I'm speaking about?
>
>
>Thanks,
>
>
>Stuart (tonight while cooking I rubbed chilli on my eyes - not
>deliberately mind you. such pain)
>
>----------
>[
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
