471,052 Members | 1,205 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,052 software developers and data experts.

how to rewrite this without subqueries?

i have a table that contains the fields order_id, canceled, and
captured. the same order_id can appear many times in the table. i
want to retrieve all the order_ids for which NO record exists that has
both canceled and captured = N.
i am not able to upgrade my version of mysql at the moment, so i would
like to write a query that does that with no subqueries.

can anyone think how??

thanks,
-k
Jul 20 '05 #1
1 1200
"karen" <pl**********@hotmail.com> wrote in message
i have a table that contains the fields order_id, canceled, and
captured. the same order_id can appear many times in the table. i
want to retrieve all the order_ids for which NO record exists that has
both canceled and captured = N.


The following should do the trick, but I'm not sure if alias columns compile
in your version of MySql.

select order_id, sum(case cancelled when 'Y' then 1 when 'N' then 0 end)
sum_cancelled
from data
group by order_id
having sum_cancelled = 0

You can also always create a new table to store the results of the
sub-query, and populate it with an insert ... select statement. Internally,
this is how MySql would handle some sub-queries. To create a temporary
table visible to only one user, do the following:

create temporary table_name ...
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Daniel Elliott | last post: by
1 post views Thread by baroque Chou | last post: by
7 posts views Thread by Stan Canepa | last post: by
3 posts views Thread by windandwaves | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.