Try a "LEFT JOIN" with Count of LEFT JOINED TABLE Joe ----- Original Message ----- From: "Houk, Gary" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, July 24, 2002 8:49 AM Subject: OT - Stored Procedure Help
> I have a header and detail table. The detail tables stores product(s) associated with the header (user information). Whenever I have more than one detail line, I get more than one record returned. I only need to get the amount of headers back that match the input, not detail. > > > -------------------------------------------------------------------------- --------- > > > CREATE PROCEDURE spWEBOrderHeaderDetailLookup > @SearchString char(10) > AS > > SELECT oh.rssc_rep_id, > oh.office_code, > oh.entry_method, > oh.new_connect_flag, > oh.shipping_method, > oh.payment_method, > oh.delivery_date, > oh.btn, > oh. btn_cust_code, > oh.credit_score, > oh.treatment_code, > oh.customer_ssn, > oh.day_phone, > oh.credit_card_type, > oh.credit_card_number, > oh.credit_card_expiration, > oh.baid, > oh.billing_name, > oh.billing_address_1, > oh.billing_address_2, > oh.billing_address_3, > oh.billing_city, > oh.billing_state, > oh.billing_zip, > oh.shipping_name, > oh.shipping_address_1, > oh.shipping_address_2, > oh.shipping_address_3, > oh.shipping_city, > oh.shipping_state, > oh.shipping_zip, > oh.update_date, > oh.update_user, > oh.vendor_code, > oh.batch_number, > oh.credit_worthiness, > oh.in_treat_indicator, > oh.updated_to_ncr, > oh.ncr_batch_number, > oh.email_address, > oh.organization, > oh.freight_amount, > oh.account_number, > oh.check_number, > oh.check_number, > oh.routing_number, > > od.product_id, > od.quantity, > od.item_price, > od.sp_product_id, > od.batch_number > > FROM WEBOrderHeader oh > > INNER JOIN WEBOrderDetail od ON oh.batch_number = od.batch_number > WHERE CONVERT(varchar, oh.batch_number) = @SearchString > OR (oh.btn = @SearchString) > GO > > -------------------------------------------------------------------------- --------- > > TIA, > > - Gary > ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
