Glad to be of assistance, this list has saved my ass many a time! Two
things, the easiest way I know to do this is just add:
<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>
But, I think there is a better way to do it with a join. However, without
trying it, I am a bit unsure about what style of join to use. It would be
something like this, but again, I am not sure it works and without testing
it......
<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>
Brook Davies
logiforms.com
At 04:22 PM 2/9/2004, you wrote:
>Actually, one other thing though, if I wanted to pull something else
>from ProductCodeTable such as ProductCodeDescription, do I have to make
>another query for that?
>
>
>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]
