SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Enthusiast mandi_nole's Avatar
    Join Date
    Jun 2001
    Location
    Orlando, FL
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL and NOT IN (SELECT...) clauses

    Can anyone tell me whether MySQL allows this kind of syntax:

    SELECT blah FROM blah WHERE blah_id NOT IN (SELECT blah_id from stupid);

    It keeps giving me a parse error. I've looked it up in the docs, but all they tell me is that IN and NOT IN clauses are viable - but not whether they can be coupled with a nested SELECT statement.

    This will be a major pain in the tail if MySQL doesn't do this.

    Please help!!!



    Mandi
    Love ya - mean it!

  2. #2
    You talkin to me? Anarchos's Avatar
    Join Date
    Oct 2000
    Location
    Austin, TX
    Posts
    1,438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think this might work:

    SELECT blah.* FROM blah,stupid WHERE blah.id != stupid.id

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No nested queries in MySQL

  4. #4
    SitePoint Enthusiast mandi_nole's Avatar
    Join Date
    Jun 2001
    Location
    Orlando, FL
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why the **** not???

    Pardon the vent, but that's vanilla ANSI-SQL...supported by all major relational database systems...

    ARGH!

    But thank you for the info.

    Mandi
    Love ya - mean it!

  5. #5
    You talkin to me? Anarchos's Avatar
    Join Date
    Oct 2000
    Location
    Austin, TX
    Posts
    1,438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Jeez you don't even need to use a nested query.

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by mandi_nole
    Why the **** not???

    Pardon the vent, but that's vanilla ANSI-SQL...supported by all major relational database systems...

    ARGH!

    But thank you for the info.

    Mandi
    Welcome to the world of MySQL!!

  7. #7
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To quote Philip Greeenspun of Arsdigita.com and MIT,
    ANSI SQL92 would be a standard if anybody followed it.


    Consider using PostegreSQL if you desire a more complete implementation of SQL.

  8. #8
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Anarchos
    I think this might work:

    SELECT blah.* FROM blah,stupid WHERE blah.id != stupid.id
    This will not provide the result set you think it might and that mandi is after.

    Remember that an inner join of two tables forms the cartesian product of the two sets. That is, every element of set A becomes joined to every element of set B.

    A (id)
    ----
    1
    2
    3

    B (id)
    ----
    1
    2
    3

    A * B
    ---
    1,1
    1,2
    1,3
    2,1
    2,2
    2,3
    3,1
    3,2
    3,3

    So the sql SELECT * FROM A,B WHERE A != B will produce
    1,2
    1,3
    2,1
    2,3
    3,1
    3,2

    Consider mandi's original SQL (my variation of)
    SELECT * FROM A WHERE A.id NOT IN (SELECT id FROM B)

    Test Data

    A (id)
    ----
    1
    2
    3

    B (id)
    -----
    3,
    4,
    5

    SELECT * FROM A,B WHERE A != B
    ------------------------
    1
    1
    1
    2
    2
    2
    3
    3

    SELECT * FROM A WHERE A.id NOT IN (SELECT id FROM B)
    -----------------------------------------
    1
    2

  9. #9
    SitePoint Enthusiast mandi_nole's Avatar
    Join Date
    Jun 2001
    Location
    Orlando, FL
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I appreciate the discussion on my behalf - thank you, gurus.

    As for the suggestion of using PostGre - the database choice wasn't mine to make. Our company is rather backwards in their decision-making about Internet projects, and the web dev team is rarely included.

    And as for different ANSI interpretations - true, they are infinite, but so far the biggest problem I've run into with larger commercial systems like Oracle, SQL Server, and FoxPro (if you can even CALL that one commercial) is trying to figure out how to format date strings...all the query syntax performing simple joins and nested queries works just fine across the board. But I'm not a DBA, so I'm sure there are other issues I'm not aware of.

    I'm trying very hard to be supportive of the opensource movement, and understanding of its limitations (caused, no doubt, by my current inability to manipulate the source code of the languages and software, itself - I'm a newbie, after all)...but darnit, this is an EASY thing to do!!!

    Thank you for all your help.

    Mandi
    Love ya - mean it!

  10. #10
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,067
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mySQL doesn't currently have support for sub-selects, but it's something they plan to introduce in a future version. PostGreSQL can do them though and version 7.1 has been getting rave reviews all over the place.

  11. #11
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    nested select in MySQL

    Hi,

    I have run into something similar. Since this is the only time I need to use a sub select I dont want to consider moving to a different database. Also the company will most probably not allow that. Since this is the only time I need to do this, i am hoping that I can get my work done through joints or something at which I am not very good. I am posting below the the nested select which I would like to execute and would like someone to post a query which would generate the same result in MySQL.

    select * from products_categories where category_id in (select id from category where parent_id=1);

    the query within the parenthesis will output a list of id which should be the category_id of the output records from the products_categories table.

    Waiting for some response.
    thanks for the help

  12. #12
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I might be wrong but is it possible to execute the query

    select id from category where parent_id=1

    and store the result in a dynamic array and then execute the query

    select * from products_categories where category_id in (results_from_dynamic_array);

    ?

    I dont know how to create a dynamic array and execute this idea.

    I will be executing this query through a php script where parent_id will be a variable input from a form.

    Any help would be greatly appreciated. thanks

  13. #13
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: nested select in MySQL

    Originally posted by tipiyanos
    select * from products_categories where category_id in (select id from category where parent_id=1);
    Code:
    SELECT *
      FROM products_categories,
           category
     WHERE category_id = id
       AND parent_id   = 1
    That should work, no?

  14. #14
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes it did.
    thanks a lot

    I was thinking it in a complex manner. It was much simpler. Thanks again.

  15. #15
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, getting back to the original posters query. I just couldn't resist playing with it. What I wanted to do was see if there was any way that you could do it with one query in MySQL. Well, I have failed there. So the next thing was comming up with the most elegant solution using two queries. Here is what I ended up with. You comments and criticisms please
    Code:
    Setting up test schema and data
    mysql> create table Table1 (id INT);
    
    mysql> create table Table2 (id INT);
    
    mysql> insert into Table1 values (1),(2),(3);
    Query OK, 3 rows affected (0.00 sec)
    
    mysql> insert into Table2 values (3),(4),(5);
    Query OK, 3 rows affected (0.00 sec)
    
    mysql> select * from Table1;
    +------+
    | id   |
    +------+
    |    1 |
    |    2 |
    |    3 |
    +------+
    3 rows in set (0.00 sec)
    
    mysql> select * from Table2;
    +------+
    | id   |
    +------+
    |    3 |
    |    4 |
    |    5 |
    +------+
    3 rows in set (0.00 sec)
    
    Testing that this query won't work
    mysql> SELECT *          
        -> FROM Table1, Table2
        -> WHERE Table1.id != Table2.id;
    +------+------+
    | id   | id   |
    +------+------+
    |    1 |    3 |
    |    2 |    3 |
    |    1 |    4 |
    |    2 |    4 |
    |    3 |    4 |
    |    1 |    5 |
    |    2 |    5 |
    |    3 |    5 |
    +------+------+
    8 rows in set (0.01 sec)
    OK - I'm not going to wrap this up into a PHP script but essentially, the idea is to run two queries. The first simply selects the count of all rows in Table1.

    I then use the value of that query in my HAVING clause in the next query:
    Code:
    mysql> SELECT COUNT(id) FROM Table2;
    +-----------+
    | COUNT(id) |
    +-----------+
    |         3 |
    +-----------+
    1 row in set (0.00 sec)
    
    mysql> SELECT Table1.id     
        -> FROM Table1, Table2
        -> WHERE Table1.id != Table2.id
        -> GROUP BY Table1.id
        -> HAVING COUNT(Table1.id) = 3;
    +------+
    | id   |
    +------+
    |    1 |
    |    2 |
    +------+
    2 rows in set (0.00 sec)
    Eureka! - we have the desired results

  16. #16
    SitePoint Enthusiast mandi_nole's Avatar
    Join Date
    Jun 2001
    Location
    Orlando, FL
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you!

    I'll give it a whirl, as soon as we rebuild the box...here's another offshoot of the original topic, but does anyone know whether changing the root password on the box (running Solaris) would affect the MySQL service account? I know that whomever installed MySQL on the box did so under the root user instead of a pseudo...

    Anyway, thank you again - it's been highly informative.

    Mandi
    Love ya - mean it!


Bookmarks

Posting Permissions

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