List:General Discussion« Previous MessageNext Message »
From:sheeri kritzer Date:March 27 2006 5:48pm
Subject:Re: Find records not in many-to-many table?
View as plain text  
Your question seems to have been answered already, so let me add one
cautionary note -- use a LIMIT clause when testing out a new query! 
Especially with a join.  A simple mistake can lead to a Cartesian
product of 2 tables -- I always do limit 100 or something so that I
can then check to see that what I get is logically correct.

(I usually craft the query, do a LIMIT, check the logic, then do a
COUNT(fields), check to make sure that sounds like the right #, and
then the final query.  Saves a lot of aborting.  By the way, aborting
the query (say, using Ctrl-C) doesn't actually stop the query, it just
stops the mysql client.  You really need to go in and kill the query
via thread id if you want the server to actually stop performing the
query.)

-Sheeri

On 3/25/06, barney <barney@stripped> wrote:
> 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
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
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