davidvilson
asked on
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_casset
How can I achieve this. plz help me.
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_casset
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>
ASKER
I want to delete from the query result set "qry_get_cassettes_only". Not from book_product table.
the qry_get_cassettes_only is name of the query not the table.
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.
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Can you please give an example of it? I did not understand what do u mean by static query?
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_
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".
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_
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".
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.
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.
<cfquery name="del">
delete from book_product
where prod_prod_id =
</cfquery>