List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 26 1999 12:20pm
Subject:Re: Need a query
View as plain text  
On Fri, 1999-09-24 12:50:28 -0600, Dan O'Reilly wrote:
> At 07:59 PM 9/24/99 +0100, Timo Maier wrote:
[...]
> >table1
> >  id int,
> >  ip char(20)
> >
> >table2
> >  id int,
> >  ip char(20)
> >  
> >I need a query which returns the records, where the ip is present
> >only in one table. I'm using mySQL for OS/2, latest release.
> 
> I don't know about MYSQL, [...]
[...]
> SELECT IP FROM TABLE2 
> 	WHERE TABLE1.IP NOT IN (SELECT DISTINCT IP FROM TABLE2) 
> UNION
> SELECT IP FROM TABLE2 
> 	WHERE TABLE2.IP NOT IN (SELECT DISTINCT IP FROM TABLE1);

Without sub-selects and unions (which aren't supported by MySQL yet as
others already pointed out), the solution is:

- instead of NOT IN (sub-select), the trick is to use LEFT JOINs and
  test for the NULL rows

  /* get ips, which are in table1 but not in table2
  SELECT table1.ip
  FROM   table1 LEFT JOIN table2 USING (ip)
  WHERE  table2.ip IS NULL;

  /* get ips, which are in table2 but not in table1
  SELECT table2.ip
  FROM   table2 LEFT JOIN table1 USING (ip)
  WHERE  table1.ip IS NULL;


- instead of UNION, just do two seperate queries and use a temporary
  table to unite the results

  /* For MySQL V3.23.x */
  CREATE TEMPORARY TABLE tmp type=heap
    SELECT table1.ip
    FROM   table1 LEFT JOIN table2 USING (ip)
    WHERE  table2.ip IS NULL;
  INSERT INTO tmp
    SELECT table2.ip
    FROM   table2 LEFT JOIN table1 USING (ip)
    WHERE  table1.ip IS NULL;
  SELECT DISTINCT * FROM tmp;
  DROP TABLE tmp;

  /* For older versions of MySQL */
  CREATE TABLE tmp ( ip CHAR(20) );
  INSERT INTO tmp
    SELECT table1.ip
    FROM   table1 LEFT JOIN table2 USING (ip)
    WHERE  table2.ip IS NULL;
  INSERT INTO tmp
    SELECT table2.ip
    FROM   table2 LEFT JOIN table1 USING (ip)
    WHERE  table1.ip IS NULL;
  SELECT DISTINCT * FROM tmp;
  DROP TABLE tmp;

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Need a query(Timo Maier)24 Sep
  • Re: Need a queryDan O'Reilly24 Sep
    • Re: Need a queryBob Kline24 Sep
      • Re: Need a queryDan O'Reilly24 Sep
    • Re: Need a queryMartin Ramsch26 Sep