• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

sql query in cfloop

Dear friends,

I have this below program.

so,my question is if the recordcount in loop2 is > 0, then I want to delete the product_id from the first queryresult(qry_get_cassettes_only) and come out of the loop2.

How can I achieve this. plz help me.
<cfquery name="qry_get_cassettes_only" datasource="RBENT" dbtype="ODBC">
	select  product.prod_prod_id, book_product.bp_bi_book_id 
    from product INNER JOIN book_product ON product.prod_prod_id = book_product.bp_prod_prod_id
	   where product.prod_med_id = '1'
       and product.prod_avail = 'Y'
</cfquery>



<cfloop query="qry_get_cassettes_only"> <!--- Loop 1 --->
<cfquery name="book_query" datasource="RBENT">select * from book_product bp2 where bp2.bp_bi_book_id = #bp_bi_book_id#</cfquery>

<cfloop query="book_query"> <!--- Loop 2 --->
<cfquery name="product_media" datasource="RBENT">select * from product p2 where p2.prod_prod_id = #prod_prod_id# and p2.prod_med_id = '2'</cfquery>
<cfif #book_query.RecordCount# GT 0> </cfif>
</cfloop>

</cfloop>

Open in new window

0
davidvilson
Asked:
davidvilson
  • 3
  • 3
  • 3
1 Solution
 
erikTsomikSystem Architect, CF programmer Commented:
just add

<cfquery name="del">
delete from book_product
where prod_prod_id  =
</cfquery>
0
 
davidvilsonAuthor Commented:
I want to delete from the query result set "qry_get_cassettes_only". Not from book_product table.
0
 
erikTsomikSystem Architect, CF programmer Commented:
the qry_get_cassettes_only is name of the query not the table.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kevin CrossChief Technology OfficerCommented:
It would seem to me that you really need to adjust the first query with something like a not exists clause to ensure that it doesn't have results in the second query if I am understanding correctly that basically you don't want any results from first query if there is at least one record for the given id in the second query.
0
 
davidvilsonAuthor Commented:
Is there any way that I can delete a record from the queryresult? i mean can i delete the same way i delete a record from a table? in that case what dsn value should I give?
0
 
erikTsomikSystem Architect, CF programmer Commented:
yes you can . Bu you need to setup a static query. and then you can delete from static query, QueryNew
0
 
davidvilsonAuthor Commented:
Can you please give an example of it? I did not understand what do u mean by static query?
0
 
Kevin CrossChief Technology OfficerCommented:
Sorry I got distracted, but here is what I am proposing -- don't even have it in the result set to begin with.

select  product.prod_prod_id, book_product.bp_bi_book_id
from product
inner join book_product
   on product.prod_prod_id = book_product.bp_prod_prod_id
where product.prod_med_id = '1' and product.prod_avail = 'Y'
and not exists (
   select 1
   from book_product bp2
   where bp2.bp_bi_book_id = book_product.bp_bi_book_id
)
;

Replace your original query with the one above and then you will get the resultset you need without having to delete anything.  As for deleting from the query, I know you can query a query, but don't believe you can delete rows from it like a normal table but I may be wrong.  To do that I would think you need to construct your own query array and basically fill it with records excluding those that are "deleted".
0
 
Kevin CrossChief Technology OfficerCommented:
Ah, QueryNew, nice erikTsomik.  
JMHO, though, why go through the hassle of bringing these records back from SQL if they are not to be used in CF.  But obviously I am here from database side and so if you truly want a CF solution, I will leave you to the capable hands of erikTsomik.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now