List:General Discussion« Previous MessageNext Message »
From:Stefan Kuhn Date:March 26 2006 3:04pm
Subject:Re: Find records not in many-to-many table?
View as plain text  
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> > 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/mysql?unsub=1

-- 
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
Thread
Find records not in many-to-many table?barney25 Mar
  • Re: Find records not in many-to-many table?Stefan Kuhn25 Mar
  • Re: Find records not in many-to-many table?sheeri kritzer27 Mar
Re: Find records not in many-to-many table?barney26 Mar
  • Re: Find records not in many-to-many table?Peter Brawley26 Mar
  • Re: Find records not in many-to-many table?Michael Stassen26 Mar
  • Re: Find records not in many-to-many table?Stefan Kuhn26 Mar
Re: Find records not in many-to-many table?barney26 Mar