How does one retrieve the rows in a select statement that DONT match the
select.
select CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog.C arIndex=BigCata log.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? 12 14075
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.C arIndex=BigCata log.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.C arIndex<>BigCat alog.CarIndex
Regards,
Shawn
--
Shawn Wilson sh***@glassgian t.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 <no****@nospam. gov> wrote: How does one retrieve the rows in a select statement that DONT match the select.
select CarIndex FROM DealerCatalog, BigCatalog WHERE DealerCatalog.C arIndex=BigCata log.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 <no****@nospam. gov> wrote:
How does one retrieve the rows in a select statement that DONT match the select.
select CarIndex FROM DealerCatalog, BigCatalog WHERE DealerCatalog .CarIndex=BigCa talog.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.C arIndex=BigCata log.CarIndex WHERE BigCatalog.CarI ndex IS NULL
kaptain kernel wrote: Tim Van Wassenhove wrote: On 2004-01-13, kaptain kernel <no****@nospam. gov> wrote:
How does one retrieve the rows in a select statement that DONT match the select.
select CarIndex FROM DealerCatalog, BigCatalog WHERE DealerCatalog.C arIndex=BigCata log.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.
No kidding. You asked for a cartesian product by specifying a join of two
tables with essentially no join condition...
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.C arIndex=BigCata log.CarIndex WHERE BigCatalog.CarI ndex IS NULL
Actually, this is definitely not the optimal way to do it, as you are doing
a join but not using any information from the second relation. This is going
to be rather slow. Instead, do:
SELECT CarIndex FROM DealerCatalog WHERE DealerCatalog.C arIndex NOT IN
(SELECT CarIndex FROM BigCatalog);
Additionally, you should make sure that there is an index on
DealerCatalog.C arIndex and also an index on BigCatalog.CarI ndex.
-Ian
or
SELECT CarIndex FROM DealerCatalog WHERE NOT EXISTS
(SELECT * FROM BigCatalog WHERE DealerCatalog.C arIndex =
BigCatalog.CarI ndex);
Uzytkownik "Agelmar" <if**********@c omcast.net> napisal w wiadomosci
news:bu******** ****@ID-30799.news.uni-berlin.de... Actually, this is definitely not the optimal way to do it, as you are
doing a join but not using any information from the second relation. This is
going to be rather slow. Instead, do:
SELECT CarIndex FROM DealerCatalog WHERE DealerCatalog.C arIndex NOT IN (SELECT CarIndex FROM BigCatalog);
Additionally, you should make sure that there is an index on DealerCatalog.C arIndex and also an index on BigCatalog.CarI ndex.
-Ian
Chung Leong wrote: or
SELECT CarIndex FROM DealerCatalog WHERE NOT EXISTS (SELECT * FROM BigCatalog WHERE DealerCatalog.C arIndex = BigCatalog.CarI ndex);
No, that is much slower. Your method will result in a sub-query on the
database for each CarIndex in DealerCatalog. My method results in the
subquery being evaluated only once, as it is not a correlated subquery. The
database retrieves a list of CarIndex tuples from the BigCatalog relation,
and it does this only once, storing this in memory. It then probes this list
for each CarIndex in DealerCatalog. With your method, it takes CarIndex
values from DealerCatalog one at a time, and for each such value, it issues
a query against BigCatalog. Not ideal.
kaptain kernel wrote: Tim Van Wassenhove wrote:
On 2004-01-13, kaptain kernel <no****@nospam. gov> wrote:
How does one retrieve the rows in a select statement that DONT match the select.
[snip[
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?
[snip] 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.C arIndex=BigCata log.CarIndex WHERE BigCatalog.CarI ndex IS NULL
If your RDBMS vendor implemented the SET operators using the
MINUS operator may prove to be much faster; e.g.
SELECT CarIndex FROM DealerCatalog
MINUS
SELECT CarIndex FROM BigCatalog
ORDER BY 1;
kaptain kernel wrote: Tim Van Wassenhove wrote: On 2004-01-13, kaptain kernel <no****@nospam. gov> wrote:
How does one retrieve the rows in a select statement that DONT match the select.
select CarIndex FROM DealerCatalog, BigCatalog WHERE DealerCatalog .CarIndex=BigCa talog.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.C arIndex=BigCata log.CarIndex WHERE BigCatalog.CarI ndex IS NULL
My apologies - I didn't notice there 2 tables. I was thinking of just a select
from a single table.
Regards,
Shawn
--
Shawn Wilson sh***@glassgian t.com http://www.glassgiant.com
I have a spam filter. Please include "PHP" in the
subject line to ensure I'll get your message.
Depends on the database software. On MS SQLServer using EXISTS is much
faster than using IN, rather counterintuitiv ely.
Uzytkownik "Agelmar" <if**********@c omcast.net> napisal w wiadomosci
news:bu******** ****@ID-30799.news.uni-berlin.de... Chung Leong wrote: or
SELECT CarIndex FROM DealerCatalog WHERE NOT EXISTS (SELECT * FROM BigCatalog WHERE DealerCatalog.C arIndex = BigCatalog.CarI ndex); No, that is much slower. Your method will result in a sub-query on the database for each CarIndex in DealerCatalog. My method results in the subquery being evaluated only once, as it is not a correlated subquery.
The database retrieves a list of CarIndex tuples from the BigCatalog relation, and it does this only once, storing this in memory. It then probes this
list for each CarIndex in DealerCatalog. With your method, it takes CarIndex values from DealerCatalog one at a time, and for each such value, it
issues a query against BigCatalog. Not ideal.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Rick |
last post by:
I need to store a date prior to the unix epoch, any pointers?
--
Rick
Digital Printing
www.intelligence-direct.com
|
by: John Fabiani |
last post by:
Hi,
I'm a newbie and I'm attempting to learn howto create a select statement.
When I use
>>> string1='18 Tadlock Place'
>>> cursor.execute("SELECT * FROM mytest where address = %s",string1)
All works as expected. But
>>> numb=10
>>> cursor.execute("SELECT * FROM mytest where clientID = %d",numb)
Traceback (innermost last):
File "<stdin>", line 1, in ?
|
by: Fatt Shin |
last post by:
Hi,
I'm running MySQL 4.0.13, connecting from PowerBuilder 9 using ODCB
Connector 3.51.
I'm facing a problem where whenever I issue a SELECT COUNT(*) statement
from PowerBuilder, I always get SQL syntax error back from MySQL. (Refer
to ODBC Trace I captured below).
metrohouse af8-b94 ENTER SQLExecDirect
HSTMT 014D2360
UCHAR * 0x020A0EA2 "select count ( *) from code
|
by: Hans Maurer |
last post by:
>Description:
We're running our current TTS application with MySQL (on Unix). All
database,
table and column names are in lower-case. However, we need to access this
database with a new application which (for some reason) converts all
table and column names in its SELECT statements to uppercase.
Since MySQL is case-sensitive on Unix, I added the statement
set-variable = lower_case_table_names=1
to /etc/my.cnf. However, statements...
|
by: Richard van Denzel |
last post by:
Hi All,
I've defined a table (m) with an double field (bedrag) in it and when
I execute the next statement:
INSERT INTO m (bedrag) VALUES('-7000,00');
the value get stored correctly.
I've written a PHP-script which does the following:
| |
by: jayson_13 |
last post by:
Hi,
I need to implement a counter and i face problem of locking so hope
that u guys can help me.
I try to do test like this :
1st connection
SELECT * FROM nextkey WHERE tblname = 'PLCN' FOR Update;
(when i execute this statement and i guess that this will lock the
|
by: pj |
last post by:
Why does M$ Query Analyzer display all numbers as positive, no matter
whether they are truly positive or negative ?
I am having to cast each column to varchar to find out if there are
any negative numbers being hidden from me :(
I tried checking Tools/Options/Connections/Use Regional Settings both
on and off, stopping and restarting M$ Query Analyer in betwixt, but
no improvement.
|
by: Hendry Taylor |
last post by:
I have a problem where if I issue a select * from against a database it
returns no data,
but if I select column from it returns the data.
Why would the * not be working as a wildcard?
|
by: abighill |
last post by:
Problem
-------------
I want to return all URL records from 'fett_url' that are not currently
indexed in the lookup table 'fett_url_to_data' where 'data_id=2'.
i.e.
fields=> url_id, url_title, url_link, url_description
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |