Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004

    Unanswered: Problem converting subquery to joins

    First let me explain the table stucture and test data.

    Simple table:
    CREATE TABLE drinks(drinkid INT, ingid INT)

    Table Contents

    I have the following sql statement which will select out all drinks that can be made from any subset of the specified ingredient id's and do not contain any ingredients that I did not specify:

    FROM drinks
    WHERE drinkid NOT IN
    (SELECT drinkid
    FROM drinks
    WHERE ingid NOT IN (1, 3, 4))

    The above query would return the drinkid's 1 and 3. It would not return drinkid 2 because it contains the ingredient 5, which was not in my list of specified ingredients.

    My problem is I cannot find a way to rewrite this query in such a way that it will run on mysql 4.0.x (ie, without the subquery)... please help!


  2. #2
    Join Date
    Jun 2004

    Here's one approach

    A series of SQL statements can do the trick.....

    create temporary table my$temp
    select distinct drinkid
    from drinks
    where ingid in (1,3,4);

    insert into my$temp
    select distinct drinkid
    from drinks
    where ingid not in (1,3,4);

    select drinkid from my$temp
    group by drinkid having count(*) < 2;

    drop table my$temp;


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts