On Oct 05, 2006, at 9:15 AM, Toby Rush wrote:
SELECT tPersons.* FROM tPersons,tHats WHERE
tHats.ownerIDNumber=tPersons.idNumber
How can I do the opposite of that? In other words, how can I get a
recordSet containing the rows that the above statement does not
return? Thanks in advance...
OK... you want all the data from tPersons where their id is NOT IN
the set of people that have a tHat
the set of people who have tHats is
select ownerIDNumber from tHats
so those that have no tHat is
select * from tPersons where idNumber not in ( select ownerIDNumber
from tHats )
Norman, you rock!
Aaron (who replied off-list) and Math, I see that your solutions
also work, though it's going to take me a little time to wrap my
brain around left outer join. I'm going to go with Norman's
solution, since it seems a lot simpler. It appears that they all
give me the same correct set of records... are there any benefits
to using one or the other?
Pro's and con's ? ... not really ...
However the solution I gave will work on most any database engine.
However, if subqueries are not optimized it could be a tad slower.
The up side is you can use distinct and an index on the subquery to
get some extra speed.
While SQL 92 and SQL 99 added a lot of syntax I've yet to find
anything that they added that I had not been doing in some other
fashion for years.
However, the join syntax is now at least standard so it's more
portable than all the various vendor mechanisms for achieving the
same thing.
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
