Entry

Does MySQL support subselects?

Can I do a subquery in a MySQL query?

Jul 2nd, 1999 18:41
Nathan Wallace, SANIsoft, Sander Pilon, Aaron Leon Kaplan

Subselects will not be available in MySQL until version 3.23.

To work around this you'll have to make multiple queries and create
temporary tables to simulate subselect behaviour, or you'd have to come
up with a 'flat' query that does the same.

The general workaround (for all DBs where there are no subqueries) is
temporary tables. First make a temp table for the subquery and then
select from that.

Below is an example that shows the original subselect query and the work
around.

Here is the query with subselect:

    SELECT bizname, contactphone, city, website, 
           logo, description, discountid
    FROM business, discount
    WHERE business.bizid=discount.bizid AND discount.bizid
    IN (SELECT bizid FROM business WHERE catid=$categoryID)
    ORDER BY bizname

Here is the work around:

    $subresult = mysql_query("SELECT bizid
                              FROM business WHERE catid=$categoryID");

    $num =mysql_Num_Rows($subresult);

    $i=0;

    $subcriteria="";

    while ($i < m)  { 
        $subcriteria .= mysql_result($subresult,$i,"bizid");
        $subcriteria .=",";
        $i++; 
    }      

    $subcriteria=substr($subcriteria,0,-1); / / Remove last comma
  
    $result = mysql_Query("
         SELECT bizname, contactphone, city, website,                   
                logo, description, discountid
         FROM   business, discount
         WHERE  business.bizid=discount.bizid AND discount.bizid 
         IN     ($subcriteria)
         ORDER BY bizname");