Re: To write a query without using a sub query

From: Helene D. <hkd2000_at_no-spam-bcpl.net>
Date: Tue, 17 Jul 2001 22:58:58 -0400
Message-ID: <3B54FB71.94A1645F@no-spam-bcpl.net>

Prasad Kulkarni wrote:

> Hello all,
>
> I am using a free database called 'MySql'. But unfortunately it does
> not support sub queries. Can some one help me to write the query
> without using the sub query.
> For example, my query is something like this:
>
> SELECT emp_name FROM emp
> WHERE emp_num > 0
> and emp_num NOT IN (SELECT supervisor_emp FROM supervisor).

If mysql supports outer joins and temporary tables, you could do this:

---

    { #Select all recs in emp and join with matching supervisor rec, if any }

     SELECT emp_num, emp_name, supervisor_emp
        FROM emp, OUTER supervisor
        WHERE emp_num = supervisor_emp
        INTO TEMP emp_temp_tbl  ;

   { #find emps that did not have a match in supervisor }    { #Note: emp_temp_tbl cols has the same names as the columns selected

      #when TEMP was defined in the select statement above. }    SELECT emp_name

        FROM emp_temp_tbl
        WHERE supervisor_emp IS NULL  ;

---

No sub-queries in mysql is a major deficiency! I'd switch into a basic Informix product like Informix-SE 5 on Linux, if I were you. I believe there may be a free version of this available from Informix.

-Helene D. Received on Tue Jul 17 2001 - 21:58:58 CDT