You can do this with a pretty simple left join as follows: SELECT MainTable.ID from MainTable LEFT JOIN MapTable ON MainTable.ID=MapTable.ID where MapTable.ID IS NULL;
Because it is a left join, it will pad all MainTable entries that don't appear in MapTable with NULL's. Hence we can check if those NULLS are there and if they are, then we know there isn't a corresponding entry in MapTable. Thus it will give us the results we want. Hope that helps. Harrison ----- Original Message ----- From: "Augey Mikus" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, May 09, 2002 5:10 PM 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
