> 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.
I'm not sure I understand the question. Maybe it would be helpful to know
what this query will be used for. The query you have here returns detail
information like the product_id, quantity and item_price ... are you using
this information after you return it to the cf page and if so, how so?
There are several ways to limit the result set to only the headers that
match your search info, but they will produce different results, i.e. You
could use a group by clause and get aggregated information from the detail
table, in other words, total number of all products ordered, total price of
all products ordered. You could remove the detail table from the query
all-together if you don't need the detail data at all. Or if you need the
individual detail data but were hoping to get it in separate columns because
you weren't sure how to display them properly on the page, you would
probably want to use an order by clause and group the output in ColdFusion
<cfoutput query="mystoredprocedure" group="btn">
... header info here ...
<cfoutput>... detail info here... </cfoutput>
</cfoutput>
hth
> 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,
fyi ^ extra space in your column definition
I almost didn't notice it, figured it was a typo. :)
Isaac Dealey
www.turnkey.to
954-776-0046
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
