On Oct 5, 2006, at 12:14 PM, Ian M. Jones 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.
You may get a speed boost (potentially huge, depending on db
engine) by
using a correlated sub-query:
select p.*
from tPersons as p
where p.idNumber not in (
select h.ownerIDNumber
from tHats as h
where h.ownerIDNumber = p.idNumber
)
This solution will be fastest in many SQL dialects (don't know about
sqlite):
select p.* from tpersons as p left outer join tHats as h on
(p.idNumber = h.ownerIDNumber )
where h.ownerIDNumber = nil
Guyren G Howe
Relevant Logic LLC
guyren-at-relevantlogic.com ~ http://relevantlogic.com
REALbasic, PHP, Python programming
PostgreSQL, MySQL database design and consulting
Technical writing and training
_______________________________________________
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>
