Won Lee
Tue, 08 Dec 2009 08:09:43 -0800
On Tue, Dec 8, 2009 at 10:35 AM, Damo Drumm <damien.dr...@quinn-group.com>wrote:
>
> This was what i meant to put in instead of qgetcustomer query
> So am I still missing a join somewhere
>
> <cfif isdefined("url.invoice")>
> <cfquery name="qgetcompanyno" datasource="#request.dsn#">
> select COMPANY_Number, INVOICE_Number, CUSTOMER_AccNum
> from INVOICE
> where INVOICE_Key = #url.invoice#
> </cfquery>
> <cfset form.INVOICE_Number = qgetcompanyno.INVOICE_Number>
> <cfset form.COMPANY_Number = qgetcompanyno.COMPANY_Number>
> </cfif>
>
>
based on the code you sent me, yes I believe you are still missing a join
in qgetinvoices
<cfquery name="qgetinvoices" datasource="#request.dsn#">
select I.INVOICE_DateAdded, I.COMPANY_Number, I.INVOICE_Number,
C.COMPANY_Name, I.INVOICE_Key, I.CUSTOMER_AccNum, CU.CUSTOMER_Name
from INVOICE I, COMPANY C, CUSTOMER CU
where I.COMPANY_Number = C.COMPANY_Number and CU.invoice_number =
I.invoice_number
</cfquery>
This all depends if invoice_number is unique in the invoice table. if it
isn't then you have more work to do. If so, this is the basic query. Then
you can add in more conditions to the where clause get only the results you
want.
In this schema, what does the company represent? the seller, the buyer,
both? Is the customer an individual or a company? if it is a company why
are there to separate tables? Not saying any of your schema is incorrect.
Just relevant information to try help you. Even if there was something
wrong with the schema not sure you could even change it right now.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328956
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4