List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:March 26 2006 4:08am
Subject:Re: Find records not in many-to-many table?
View as plain text  
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

Well, that's what happens when you don't include relevant information, such as 
your table definitions, in your question.

No, you don't need to modify your tables.  You just need to use the join syntax 
that fits your situation.  USING works when the join column has the same name in 
each table.  Otherwise, you need to use ON.  Hence, you need something like

   SELECT FileKey
   FROM dl_files
   LEFT JOIN dl_merges ON dl_files.ID = dl_merges.FileID
   WHERE FileID IS NULL;

See the manual for details <http://dev.mysql.com/doc/refman/4.1/en/join.html>.

Michael
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