List:General Discussion« Previous MessageNext Message »
From:Stefan Kuhn Date:March 25 2006 7:59pm
Subject:Re: Find records not in many-to-many table?
View as plain text  
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
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