Professional Web Applications Themes

HELP with SQL subselect - MySQL

i am trying a query like select email from table_1 where email not in (select email from table_2); for some reason, i am getting (at least one) email rows returned which also appear in table_2 ... i have tried using LOWER(email) for both selects, thought the email i am getting is all lowercase in both tables. my goal is to only return emails that appear in table_1 but not if they also appear in table_2. is there some way to handle this with LIKE or am i missing something in that these are 'chars' (could the in emails be causing ...

  1. #1

    Default HELP with SQL subselect

    i am trying a query like

    select email from table_1 where email not in (select email from
    table_2);


    for some reason, i am getting (at least one) email rows returned which
    also appear in table_2 ...
    i have tried using LOWER(email) for both selects, thought the email i
    am getting is all lowercase in both tables.

    my goal is to only return emails that appear in table_1 but not if they
    also appear in table_2.

    is there some way to handle this with LIKE or am i missing something in
    that these are 'chars' (could the in emails be causing problems)?


    might there be some other way to do this - using mysql 5.x


    many thanks,
    s7

    starman7@hotmail.com Guest

  2. #2

    Default Re: HELP with SQL subselect

    com wrote:
     
    This can be faster than subselects...

    first, need more information - like what is the relationship between table1 and
    table2?

    select a.email from table1 a left join table2 b on a.email=b.email where b.email
    is null;


    --
    Michael Austin.
    Database Consultant
    Michael Guest

  3. #3

    Default Re: HELP with SQL subselect


    Michael Austin wrote: 
    > This can be faster than subselects...
    >
    > first, need more information - like what is the relationship between table1 and
    > table2?
    >
    > select a.email from table1 a left join table2 b on a.email=b.email where b.email
    > is null;[/ref]


    thanks for the reply michael -

    there is no relationship between the two tables - only the email which
    may be the same in either - with the added problem that the case may be
    different - any way to deal with this (w/ the lower keyword)?

    also to add another wrinkle, i would actually like to exclude from a
    third table - can two left joins be used? it's less complex with two
    NOT IN subselects (would that be an OR or an AND between the two
    subselects?) - anyway - i used your example and still an email address
    that was in table2 and also in table1 is coming back in my resultset
    (it's the same case in both tables)

    the columns are both varchar255 - and there are no spaces in either
    result.

    select a.email from t1 a left join t2 b on a.email=b.email where
    b.email
    is null;


    thanks again,
    s7

     

    starman7@hotmail.com Guest

  4. #4

    Default Re: HELP with SQL subselect

    com wrote:
     
    >>
    >>This can be faster than subselects...
    >>
    >>first, need more information - like what is the relationship between table1 and
    >>table2?
    >>
    >>select a.email from table1 a left join table2 b on a.email=b.email where b.email
    >>is null;[/ref]
    >
    >
    >
    > thanks for the reply michael -
    >
    > there is no relationship between the two tables - only the email which
    > may be the same in either - with the added problem that the case may be
    > different - any way to deal with this (w/ the lower keyword)?
    >
    > also to add another wrinkle, i would actually like to exclude from a
    > third table - can two left joins be used? it's less complex with two
    > NOT IN subselects (would that be an OR or an AND between the two
    > subselects?) - anyway - i used your example and still an email address
    > that was in table2 and also in table1 is coming back in my resultset
    > (it's the same case in both tables)
    >
    > the columns are both varchar255 - and there are no spaces in either
    > result.
    >
    > select a.email from t1 a left join t2 b on a.email=b.email where
    > b.email
    > is null;
    >
    >
    > thanks again,
    > s7
    >
    >

    >
    >[/ref]

    an easier way to deal with email addresses is to use lower() when you insert
    them, that way there is less data processing when needing to do the selects

    (to modify what you currently have simply:
    update tablea set email=lower(email);
    this will change all email address to lower case. If you are using a web-page
    to insert/query/login use a function to lowercase the user input to match the
    database.
    )

    adding where clause an

    select a.email,b.email,c.email from t1 a
    left join t2 b
    on lower(a.email)=lower(b.email)
    left join t3 c
    on lower(a.email)=lower(c.email);
    +---------+---------+---------+
    | email | email | email |
    +---------+---------+---------+
    | ab.com | ab.com | ab.com | <<exists in all 3
    | bc.com | bc.com | NULL | <<exists in t1,t2
    | cd.com | NULL | cd.com | <<exists in t1,t3
    +---------+---------+---------+
    3 rows in set (0.04 sec)

    The BASE query;
    mysql> select a.email,b.email,c.email from t1 a left join t2 b on lower(a.email
    )=lower(b.email) left join t3 c on lower(a.email)=lower(c.email);
    +---------+---------+---------+
    | email | email | email |
    +---------+---------+---------+
    | ab.com | ab.com | ab.com |
    | bc.com | bc.com | NULL |
    | cd.com | NULL | cd.com |
    +---------+---------+---------+
    3 rows in set (0.04 sec)

    ROWS in A but may not exist in B or in C
    mysql> select a.email,b.email,c.email from t1 a left join t2 b on lower(a.email
    )=lower(b.email) left join t3 c on lower(a.email)=lower(c.email) where b.email i
    s null or c.email is null;
    +---------+---------+---------+
    | email | email | email |
    +---------+---------+---------+
    | bc.com | bc.com | NULL |
    | cd.com | NULL | cd.com |
    +---------+---------+---------+
    2 rows in set (0.02 sec)

    ROWS in a but may exist in b or c
    mysql> select a.email,b.email,c.email from t1 a left join t2 b on lower(a.email
    )=lower(b.email) left join t3 c on lower(a.email)=lower(c.email) where b.email i
    s not null or c.email is not null;
    +---------+---------+---------+
    | email | email | email |
    +---------+---------+---------+
    | ab.com | ab.com | ab.com |
    | bc.com | bc.com | NULL |
    | cd.com | NULL | cd.com |
    +---------+---------+---------+
    3 rows in set (0.04 sec)


    Records that exist in all 3 tables;
    mysql> select a.email,b.email,c.email from t1 a left join t2 b on lower(a.email
    )=lower(b.email) left join t3 c on lower(a.email)=lower(c.email) where b.email i
    s not null and c.email is not null;
    +---------+---------+---------+
    | email | email | email |
    +---------+---------+---------+
    | ab.com | ab.com | ab.com |
    +---------+---------+---------+
    1 row in set (0.08 sec)


    or this will find rows where the email ONLY exists in t1.
    First - let's add a row to t1 only:

    mysql> insert into t1 values ('df.com');
    Query OK, 1 row affected (0.22 sec)

    select f.email from t1 f where f.email not in (
    select a.email
    from t1 a left outer join t2 b
    on a.email=b.email
    left outer join t3 c
    on a.email=c.email
    where b.email is not null or c.email is not null
    );

    +---------+
    | email |
    +---------+
    | df.com |
    +---------+


    --
    Michael Austin.
    DBA Consultant
    Donations welcomed. Http://www.firstdbasource.com/donations.html
    :)
    Michael Guest

Similar Threads

  1. Subselect confusion
    By Derek in forum MySQL
    Replies: 11
    Last Post: September 29th, 02:25 PM
  2. Writing SQL query without subselect
    By Steven Stern in forum PHP Development
    Replies: 15
    Last Post: October 15th, 08:27 AM
  3. subselect argh!
    By Getho in forum Macromedia Freehand
    Replies: 7
    Last Post: April 6th, 07:13 PM
  4. 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

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