Professional Web Applications Themes

Subselect confusion - MySQL

I have a table that contains these numbers in a column: mysql> select distinct course_id from programme_courses; +-----------+ | course_id | +-----------+ | 1 | | -1 | | -2 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 65 | | 66 | | 67 | | 68 | +-----------+ 14 rows in set (0.00 sec) and I have these values in a different table: mysql> select course_id from courses; +-----------+ | course_id | +-----------+ | 65 | | 66 | | 67 | +-----------+ ...

  1. #1

    Default Subselect confusion

    I have a table that contains these numbers in a column:

    mysql> select distinct course_id from programme_courses;
    +-----------+
    | course_id |
    +-----------+
    | 1 |
    | -1 |
    | -2 |
    | 2 |
    | 3 |
    | 4 |
    | 5 |
    | 6 |
    | 7 |
    | 8 |
    | 65 |
    | 66 |
    | 67 |
    | 68 |
    +-----------+
    14 rows in set (0.00 sec)

    and I have these values in a different table:

    mysql> select course_id from courses;
    +-----------+
    | course_id |
    +-----------+
    | 65 |
    | 66 |
    | 67 |
    +-----------+
    3 rows in set (0.00 sec)

    I'm looking to find those values in the first query that aren't in the
    second, so I tried this:

    mysql> select distinct programme_courses.course_id from
    programme_courses where course_id not in (select course_id from courses);
    +-----------+
    | course_id |
    +-----------+
    | 1 |
    +-----------+
    1 row in set (0.00 sec)

    which somewhat puzzles me. If I replace the subquery with the values I
    know it returns, it works as required:

    mysql> select distinct programme_courses.course_id from
    programme_courses where course_id not in (65,66,67);
    +-----------+
    | course_id |
    +-----------+
    | 1 |
    | -1 |
    | -2 |
    | 2 |
    | 3 |
    | 4 |
    | 5 |
    | 6 |
    | 7 |
    | 8 |
    | 68 |
    +-----------+
    11 rows in set (0.00 sec)

    Can someone explain where I'm going wrong with my subquery?
    Derek Guest

  2. #2

    Default Re: Subselect confusion


    Derek Fountain wrote: 
     

    select distinct programme_courses.course_id from
    programme_courses where

    programme_courses.course_id

    not in (select course_id from courses);

    strawberry Guest

  3. #3

    Default Re: Subselect confusion

    > select distinct programme_courses.course_id from 

    That doesn't make any difference!
    Derek Guest

  4. #4

    Default Re: Subselect confusion

    Derek Fountain wrote: 
    >
    > That doesn't make any difference![/ref]

    It's something to do with primary keys. I wrote this standalone test:

    ---
    create table t1 ( course_id int(10) signed not null, primary key
    (course_id) );
    create table t2 ( course_id int(10) unsigned not null, primary key
    (course_id) );

    insert into t1 values
    (1),(-1),(-2),(2),(3),(4),(5),(6),(7),(8),(65),(66),(67),(68) ;
    insert into t2 values (65),(66),(67);

    select distinct course_id from t1 where course_id not in (select
    course_id from t2);

    drop table t1;
    drop table t2;
    ---

    which demonstrates the problem much as I have it set up in my real DB.
    Replace the subquery with 65,66,67 and it works.

    However, it also works if you remove the primary key clause from t2. If
    you just remove the primary key from t1 the result comes back as 1.

    <scratches head>

    This is MySQL 4.1.13 running on SUSE Linux 10.0.
    Derek Guest

  5. #5

    Default Re: Subselect confusion

    Derek Fountain wrote: 

    Why bother with a subquery:

    SELECT DISTINCT `programme_courses`.course_id
    FROM `programme_courses`
    LEFT JOIN `courses` USING ( course_id )
    WHERE `courses`.course_id IS NULL

    Captain Guest

  6. #6

    Default Re: Subselect confusion

    Does this work?

    SELECT DISTINCT c.course_id FROM programme_courses pc
    RIGHT JOIN courses c ON c.course_id = pc.course_id
    WHERE ISNULL(pc.course_id);

    strawberry Guest

  7. #7

    Default Re: Subselect confusion

    > Why bother with a subquery: 

    Heh, I'm rather new to this. I thought there might be smart way to do it.

    However, I'm pretty sure I've uncovered a bug. There is something
    similar in the MySQL bugs DB from about a year ago (#10649) which wasn't
    resolved. I'm currently trying to find a more up to date MySQL
    installation (> 4.1.13) to see if the issue still appears. If it does
    I'll raise it as a bug.

    I'm the meantime, thanks for showing me how to do it properly. :o)
    Derek Guest

  8. #8

    Default Re: Subselect confusion


    Derek Fountain wrote: 
    >
    > Heh, I'm rather new to this. I thought there might be smart way to do it.
    >
    > However, I'm pretty sure I've uncovered a bug. There is something
    > similar in the MySQL bugs DB from about a year ago (#10649) which wasn't
    > resolved. I'm currently trying to find a more up to date MySQL
    > installation (> 4.1.13) to see if the issue still appears. If it does
    > I'll raise it as a bug.
    >
    > I'm the meantime, thanks for showing me how to do it properly. :o)[/ref]

    It's the way most folks are forced to do it 'cos MySQL has only just
    recently (as of 4.1 I believe) started supporting subqueries! But it is
    also (usually) more efficient than a subquery, particularly when good
    indexes are used. See
    http://www.databasejournal.com/features/mysql/article.php/3434641 for a
    discussion on it.

    Captain Guest

  9. #9

    Default Re: Subselect confusion


    strawberry wrote: 

    No because you've got the databases the wrong way round.

    See my earlier post for a working version

    Captain Guest

  10. #10

    Default Re: Subselect confusion


    Captain Paralytic wrote: 
    >
    > No because you've got the databases the wrong way round.
    >
    > See my earlier post for a working version[/ref]

    Oops!

    SELECT DISTINCT pc.course_id
    FROM courses c
    RIGHT JOIN programme_courses pc ON c.course_id = pc.course_id
    WHERE ISNULL( c.course_id )

    - or just like the man already said

    strawberry Guest

  11. #11

    Default Re: Subselect confusion

    Derek Fountain wrote: 

    Since you have columns names that appear in both tables, I would
    recommend specifying correlation names:

    SELECT DISTINCT p.course_id
    FROM programme_courses AS p
    WHERE p.course_id NOT IN (SELECT c.course_id FROM courses AS c);

    However, that doesn't explain why you got even one row back from the
    query as you had it. What version of MySQL are you using? It's worth
    checking if there is a bug reported at bugs.mysql.com related to subqueries.

    You can also get the query results you want, without using a subquery:

    SELECT DISTINCT p.course_id
    FROM programme_courses AS p
    LEFT OUTER JOIN courses AS c USING (course_id)
    WHERE c.course_id IS NULL;

    Regards,
    Bill K.
    Bill Guest

  12. #12

    Default Re: Subselect confusion

    > SELECT DISTINCT pc.course_id 

    Yes, that works, as does the LEFT JOIN version. Thanks. :o)
    Derek Guest

Similar Threads

  1. Writing SQL query without subselect
    By Steven Stern in forum PHP Development
    Replies: 15
    Last Post: October 15th, 08:27 AM
  2. subselect argh!
    By Getho in forum Macromedia Freehand
    Replies: 7
    Last Post: April 6th, 07:13 PM
  3. Calling a Java SP with an embeded subselect crashes DB
    By Brien Schultz in forum IBM DB2
    Replies: 0
    Last Post: July 18th, 09:06 PM
  4. Replies: 1
    Last Post: June 25th, 12:14 AM
  5. index usage on view union, with subselect
    By bung ho in forum Oracle Server
    Replies: 3
    Last Post: December 19th, 08:22 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139