Professional Web Applications Themes

simple sql multiple-table select question - MySQL

I'm a beginner with SQL programming, so please bear with me. I'm having the following issue: SELECT table1.id FROM table1, table2 WHERE table1.id<>table2.id For whatever reason this query returns two of each id from table1 except where it equals the id from table2, in which case it only returns one id. The id columns contain unique values and I don't understand why it's returning two of each instead of one row for each id that doesn't match the IDs from table2. I just want all the IDs that aren't also in table2 returned. Any help would be appreciated. Thanks!...

Sponsored Links
  1. #1

    Default simple sql multiple-table select question

    I'm a beginner with SQL programming, so please bear with me. I'm
    having the following issue:

    SELECT table1.id FROM table1, table2 WHERE table1.id<>table2.id


    For whatever reason this query returns two of each id from table1
    except where it equals the id from table2, in which case it only
    returns one id.

    The id columns contain unique values and I don't understand why it's
    returning two of each instead of one row for each id that doesn't
    match the IDs from table2. I just want all the IDs that aren't also in
    table2 returned.

    Any help would be appreciated. Thanks!

    Sponsored Links
    rdlowrey@gmail.com Guest

  2. #2

    Default Re: simple sql multiple-table select question

    com wrote: 

    Do you have two (or three) rows in table2?

    What's happening is for each row in table1 it's looking for a
    non-matching row in table2. So if both table1 and table2 had (1, 2, 3)
    as id's, it would compare:

    1!=1 - false
    1!=2 - true
    1!=3 - true
    2!=1 - true
    2!=2 - false
    2!=3 - true
    3!=1 - true
    3!=2 - true
    3!=3 - false

    And print the true values


    You could do it with a subselect (Mysql 4 & above), i.e.

    SELECT id FROM table1
    WHERE id NOT IN (SELECT id FROM table2)

    Not too bad for small tables, but MySQL doesn't optimize this very well
    and as your tables grow you'll have longer and longer searches.

    A better way would be to use a LEFT JOIN to do it. This returns all
    values for table1 and matching values for table2. If there is no
    matching value in table2, it returns NULL. You can then just pick out
    the ones where NULL is returned in table2, i.e.

    SELECT id FROM table1
    LEFT JOIN table2 ON table1.id = table2.id
    WHERE table2.id is NULL

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: simple sql multiple-table select question

    Thanks Jerry! I appreciate it!

    rdlowrey Guest

Similar Threads

  1. Distinct SELECT to query multiple table
    By php_Boi in forum MySQL
    Replies: 3
    Last Post: January 17th, 05:29 PM
  2. (Oracle SQL) Select multiple columns from table
    By sinclairc5 in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: November 30th, 03:23 AM
  3. Select ... INTO TEMP Table question
    By Steve in forum Informix
    Replies: 4
    Last Post: February 25th, 05:56 AM
  4. Replies: 0
    Last Post: September 24th, 03:24 AM
  5. Replies: 2
    Last Post: June 30th, 10:02 PM

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