How does one retrieve the rows in a select statement that DONT match the
select.
select CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog.CarIndex=BigCatalog.CarIndex
finds all the cars in the dealer catalog that are in the bigger distributor
catalog.
How do I do the opposite in a single sql statement i.e. all the dealer cars
that AREN''T in the big distributor catalog?
Is there a negative Select?
解决方案kaptain kernel wrote:How does one retrieve the rows in a select statement that DONT match the
select.
select CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog.CarIndex=BigCatalog.CarIndex
finds all the cars in the dealer catalog that are in the bigger distributor
catalog.
How do I do the opposite in a single sql statement i.e. all the dealer cars
that AREN''T in the big distributor catalog?
Is there a negative Select?
select CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog.CarIndex<>BigCatalog.CarIndex
Regards,
Shawn
--
Shawn Wilson
sh***@glassgiant.com
http://www.glassgiant.com
I have a spam filter. Please include "PHP" in the
subject line to ensure I''ll get your message.
On 2004-01-13, kaptain kernel wrote:How does one retrieve the rows in a select statement that DONT match the
select.
select CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog.CarIndex=BigCatalog.CarIndex
finds all the cars in the dealer catalog that are in the bigger distributor
catalog.
How do I do the opposite in a single sql statement i.e. all the dealer cars
that AREN''T in the big distributor catalog?
Is there a negative Select?
Lookup the section about the WHERE clause in your database manual.
In this case it''s enough to reverse the condition.
More general you might use the not in with subquery construct.
--
http://home.mysth.be/~timvw
Tim Van Wassenhove wrote:On 2004-01-13, kaptain kernel wrote:
How does one retrieve the rows in a select statement that DONT match the
select.
select CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog.CarIndex=BigCatalog.CarIndex
finds all the cars in the dealer catalog that are in the bigger distributor
catalog.
How do I do the opposite in a single sql statement i.e. all the dealer cars
that AREN''T in the big distributor catalog?
Is there a negative Select?
Lookup the section about the WHERE clause in your database manual.
In this case it''s enough to reverse the condition.
More general you might use the not in with subquery construct.
The suggestions mentioned above are certainly not speedy - I had to kill
a sql query when i changed the = in my original statement to <> as
suggested above. Which tells me that the suggestions are wrong.
The answer is to use LEFT JOIN - anything that doesn''t join is given a
NULL value , and it''s a heck of a lot speedier of large datasets (i''ve
got 12,000 records):
SELECT CarIndex FROM DealerCatalog LEFT JOIN BigCatalog ON
DealerCatalog.CarIndex=BigCatalog.CarIndex WHERE BigCatalog.CarIndex IS NULL