> 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