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