List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 30 1999 10:44pm
Subject:EXISTS, NOT EXISTS
View as plain text  
>>>>> "jussi" == jussi jaaskelainen <Jussi> writes:

jussi> Hi!
jussi> We have some plans to move our SQL-databases from MSSql 7.0 to
jussi> MySql-linux.
jussi> Problem is that (according documentations) MySql don't support
jussi> SQL-commands "EXISTS" and "NOT EXISTS" what our code is full-off...
jussi> 1) Any plans to include those commands to MySql-server
jussi> 2) Posibility to go around problem with some SQL-code modifications...

jussi> F.Ex.

jussi>       'SELECT * FROM SpotBlocks97 ulkosp WHERE EXISTS ('
jussi>       ' SELECT * FROM SpotBlocks97 sp, RADIOID97 rd, SUBGROUPS97 sb'
jussi>       ' WHERE'
jussi>       '      sp.active=1 AND'
jussi>       '      rd.id=:BoxID AND'
jussi>       '      (sp.enddate is null or (sp.enddate is not null AND
sp.enddate> =:Curdate)) AND'
jussi>       '      (sp._group is null OR'
jussi>       '      (sp._group=rd._group AND'
jussi>       '      (sp.subgroup is null or (sp.subgroup IN'
jussi>       '      (SELECT DISTINCT subgroup from boxsubgroups97 where
jussi> Maingroup=rd._group and ID=rd.id)'
jussi>       '     ))))'
jussi>       '  AND sp.Number=ulkosp.Number'
jussi>       ')'
jussi>       'ORDER BY 1'

Hi!

As long as your columns are defined as NOT NULL, you can use LEFT JOIN
instead of NOT exists and DISTINCT as EXISTS. In case of complicated
queries, you can always use temporary tables:

(NOT EXISTS is easy, EXIST is a little harder)

CREATE TEMPORARY TABLE t1 (Number uint not null primary_key(Number))
  SELECT sp.Number as Number FROM SpotBlocks97 sp, RADIOID97 rd,
		                  SUBGROUPS97 sb,
                                  boxsubgroups97
  WHERE sp.active=1 AND rd.id=:BoxID AND
      (sp.enddate is null or
      (sp.enddate is not null AND sp.enddate>=:Curdate)) AND
      (sp._group is null OR
        (sp._group=rd._group AND
         (sp.subgroup is null or (sp.subgroup = boxsubgroups97.subgroup and 
          boxsubgroups97.Maingroup=rd._group and boxsubgroups97.ID=rd.id))));

SELECT DISTINCT ulkosp.* FROM SpotBlocks97 ulkosp,t1 WHERE t1.Number=ulkosp.Number;
DROP TABLE t1;

(Hope I got it right..., but the idea should work)

EXISTS AND NOT EXISTS will be implemented in MySQL 3.24 (at the same
time as sub selects)

Regards,
Monty
Thread
EXISTS, NOT EXISTSJussi Jääskeläinen30 Aug
  • EXISTS, NOT EXISTSsinisa31 Aug
  • Re: EXISTS, NOT EXISTSMartin Ramsch31 Aug
    • Re: EXISTS, NOT EXISTSMichael Widenius31 Aug
  • EXISTS, NOT EXISTSMichael Widenius31 Aug
  • Re: EXISTS, NOT EXISTSJussi Jääskeläinen31 Aug
    • Re: EXISTS, NOT EXISTSJani Tolonen31 Aug
      • Re: EXISTS, NOT EXISTSBenjamin Pflugmann1 Sep
        • Re: EXISTS, NOT EXISTSJani Tolonen1 Sep
          • Re: EXISTS, NOT EXISTSMichael Widenius1 Sep
  • [Fwd: mysql 3.23.49 porting problem on POSIX 1003.1-2001 hosts]Michael Widenius2 Mar