Obviously you can do a join when the names of id columns are different as well. Look in the doc for that. This has nothing to do with your problem of finding rows not in another table - it is a basic sql thing. Stefan
Am Sunday 26 March 2006 00:47 schrieb barney: > Thanks, Stefan, > > But that only works if both tables have the same field name, doesn't it? > If I use select FileKey from dl_files left join dl_merges using (FileID) > where FileID is null MySQL returns > Unknown column 'articles.dl_files.FileID' in 'on clause'. > > Correct me if I'm wrong, but wouldn't I have to modify the column name in > one of the tables in order for that to work? Or is there a syntax in the > join lexicon that I can use to alias one of the column names within the > query? I can't modify the existing table structure(s). > > Apologies if I'm being dense. > > Make a good day ... > ... barn > > > Use > > > > select first_table.id from first_table left join second_table using (id) > > where second_table.id is null > > > > Stefan > > > > Am Saturday 25 March 2006 19:10 schrieb barney: > > > Folk, > > > > > > This may be off-base for this list, but I've run out of places to look, > > > sorry. I can't seem to find this anywhere, although I'm certain I've > > > seen it before. > > > > > > How can I identify all the records in a table that are not referenced > > > in a many-to-many table? > > > > > > I have a [unique] table of files and a [unique] table of attributes. > > > These are linked in a merge table which is many-to-many. I need to > > > find all items in the file table that are not referenced in the merge > > > table in order to add appropriate attributes for those records. The > > > attribute list is 26-30 records and the file table is currently about > > > 3,200 records, which could make for a merge table of 96,000 records. > > > > > > I tried using an IN statement against a sub-select of unique file ids > > > in the merge table, but either that will not work or I did not craft it > > > properly The query hit 6 million records before I aborted it <sigh />. > > > > > > I'm certain this can be done ... I seem to remember a similar process > > > from the DB2 corporate days ... but I just cannot wrap my head around > > > it. Anybody have any ideas, please? > > > > > > Make a good day ... > > > ... barn > > > > -- > > Stefan Kuhn M. A. > > Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) > > Z�lpicher Str. 47, 50674 Cologne > > Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 > > My public PGP key is available at http://pgp.mit.edu > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
