MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Harald Fuchs Date:July 11 2004 1:00pm
Subject:Re: Which records are not contained in 2 different tables?
View as plain text  
In article <4.3.2.7.2.20040710165624.04104a58@stripped>,
Jeff Gannaway <mysql@stripped> writes:

> I've got one huge table (table a), and two smaller tables (tables b and c)
> I need to find which records in 'table a' are not in 'table b' nor are
> they in 'table c'.

> The Primary Key for all 3 tables is 'ProductID'.

> I looked at the LEFT JOIN command in the docs, but it looks like you
> can only compare 1 table to 1 table.

> How do I do this?

With MySQL 4.1.x you can do

  SELECT a, b
  FROM a
  LEFT JOIN (SELECT b FROM b UNION SELECT c FROM c) AS x ON x.b = a.a
  WHERE x.b IS NULL;

The workaround for older versions would be a temporary table.

Thread
Which records are not contained in 2 different tables?Jeff Gannaway11 Jul
  • Re: Which records are not contained in 2 different tables?Harald Fuchs11 Jul
Re: Which records are not contained in 2 different tables?SGreen12 Jul