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'

<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>


Open in new window

Who is Participating?
erikTsomikConnect With a Mentor System Architect, CF programmer Commented:
yes you can . Bu you need to setup a static query. and then you can delete from static query, QueryNew
erikTsomikSystem Architect, CF programmer Commented:
just add

<cfquery name="del">
delete from book_product
where prod_prod_id  =
davidvilsonAuthor Commented:
I want to delete from the query result set "qry_get_cassettes_only". Not from book_product table.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

erikTsomikSystem Architect, CF programmer Commented:
the qry_get_cassettes_only is name of the query not the table.
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.
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?
davidvilsonAuthor Commented:
Can you please give an example of it? I did not understand what do u mean by static query?
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".
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.