From: Peter Brawley
Date: March 26 2006 3:26am
Subject: Re: Find records not in many-to-many table?
List-Archive: http://lists.mysql.com/mysql/196169
Message-Id: <442609D4.6060906@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------040505040801020007050403"

--------------040505040801020007050403
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

barney

>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).

If the join columns have different names, simply change the join to

select FileKey 
from dl_files f
left join dl_merges m on f.fileId = m.<whatevercolumnname>
where m.<whatevercolumnname> is null

PB

-----

barney wrote:
> 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/mysql?unsub=barney@stripped
>>     
>
>
>   

--------------040505040801020007050403--