List:General Discussion« Previous MessageNext Message »
From:dave-mlist Date:August 22 2000 7:22pm
Subject:can I do a single natural left join on 3 tables at once?
View as plain text  
I have a big table A, a big table B, and a little table C.
I want to find the first 1000 rows in A that don't exist in B or C.

I know how to find the first 1000 rows in A that don't exist in B:

INSERT INTO D
  SELECT a.*
  FROM a NATURAL LEFT JOIN b
  WHERE b.some_id IS NULL
  ORDER BY a.time DESC
  LIMIT 1000;

The above works fine for me.  But now I have to take C into account.
If I have to separate the B pass into an intermediate stage, I may
have to keep repeating B passes until the C pass has enough rows in
it.  So, I would like to do just a single step to avoid having all
that extra logic.

Is the following correct if I also have C:

INSERT INTO D
  SELECT a.*
  FROM a NATURAL LEFT JOIN b NATURAL LEFT JOIN c
  WHERE b.some_id IS NULL AND c.some_id IS NULL
  ORDER BY a.time DESC
  LIMIT 1000;

What about this:

INSERT INTO D
  SELECT a.*
  FROM a NATURAL LEFT JOIN b, c
  WHERE b.some_id IS NULL AND c.some_id IS NULL
  ORDER BY a.time DESC
  LIMIT 1000;

Thanks,
Dave
Thread
Help with LIMIT on a select with a left joinDavid BORDAS22 Aug
  • can I do a single natural left join on 3 tables at once?dave-mlist22 Aug
    • Re: can I do a single natural left join on 3 tables at once?Benjamin Pflugmann25 Aug
  • Re: can I do a single natural left join on 3 tables at once?dave-mlist25 Aug