Bizarre SQL select query
 

Here's something that's been bugging me with SQL recently. Is it possible to create a single query that that can create a list of one non-unique value which *never* has a certain corresponding value in another field? ok.. doesn't make sense... take this table:

and to derive the results
i.e a list of A which have NOT ever had a "yes" next to them. I know this is pretty easy to find using a subquery (i.e. find all the ones that HAVE had a yes and subtract that from a full list) and the equivalent join too, however I'm wondering if there's a way to do this with just ONE query, possibly using a small inline function to make any "yes"'s be selected as the result of an aggregate of some sort, and then lose those from a HAVING or such.....

Give me 2 hours, I think I might know how to do it but before posting something that makes me look like an idiot I am going to finish up the code I am writing and test my solution:)

select distinct A from table where B='no' and A not in (select distinct A from table where B='yes')

That should be it, but using 'in' can be a little slow.... but I guess if you can make an OUTER JOIN, you could ask it to select A NOT MATCHING As NOT in the other select (A where B='yes').

thanks eantoranz, but that's exactly what i don't want.... you are using a subquery, which i am trying to avoid. :)

In Oracle the fastest solution is to use minus:

SELECT fld1 from table1
minus
SELECT fld1 from table1 where
fld2='YES';

This executes fast - the reason is that you MUST do a full-table scan to complete the query anyway. So both 'selects' actually use data from a single full table scan. Sub-queries are not necessarily evil. In this instance (using Oracle) the performance will be the same whether you use the above query or something more complex. You may even degrade performance by using something else.

How about the outer join?

I think you need to use a second query such as:
http://www.mysql.com/doc/en/EXISTS_a...ubqueries.html