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
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
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
