SELECT main.id
FROM MainTable main OUTER JOIN MapTable map ON main.id = map.id
WHERE map.id = null
Does this work? Give it a whirl.
Another solution could be to do it like this:
SELECT col1, col2, coln
FROM MainTable
WHERE id IN (
SELECT id
FROM MainTable
WHERE id NOT IN (
SELECT id
FROM MapTable
)
);
Does MySQL support a MINUS like command like Oracle?
Try them both and report back on performance.
Martin Anderson
QA Engineer
ProfitLogic
Eleven Cambridge Center
Cambridge, MA 02142
t: 617.218.1946
-----Original Message-----
From: Augey Mikus [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 5:11 PM
To: [EMAIL PROTECTED]
Subject: Most efficient query
I have two tables. One table, which (to protect the innocent :-)) we'll
call MainTable, has a list of names with user info like address, etc..
and an auto-incrementing unique id for each. The other, which we'll
call MapTable, is simply a key mapping table with ids from MainTable
that just basically holds a list of MainTable ids to specify some sort
of property (be it that they are disabled or whatever)
Table: MainTable
ID | NAME
--------------------------------
1 Foo
2 Bar
Table: MapTable
ID
-------
2 <-- referring to the MainTable ID field
I am trying to query the list of records in MainTable that are NOT
mapped in MapTable. The query I am using is as follows:
select MainTable.ID from MainTable,MapTable where MainTable.ID !=
MapTable.ID;
this query works perfectly on a table with 10 records but when you get
into the millions it takes quite a long time. Is there a faster way to
get what I want?
Thanks,
Augey
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
