MySQL with multiple WHERE clauses

Question

I've a db with this info (example)

EMAIL                 COMING    
info@domain.com       google
test@example.net      google
text@example.net      yahoo
info@super.net        google
testing@foo.com       yahoo
testing@foo.com       google

I would extract ONLY value that are from "google" for example and not from "yahoo". So, in my SQL, i would read only

info@domain.com

and

info@super.net

because the other are FROM google && yahoo.

So, in code, a thing like

SELECT * FROM mytable WHERE COMING = 'google' AND COMING != 'yahoo'

but obviously my code is wrong.

Could you help me? Thank you :)

from is a reserved word in mysql. Try `\`from\``, or better yet change the column name — 1615903, May 06 '13 at 12:11
Keep in mind that a normal `SELECT` with `WHERE` clause filters on a row-by-row basis and will not take other rows into account. — tadman, May 06 '13 at 12:16

zessx · Accepted Answer · 2013-05-06T12:19:34.500

2

FROM is a reserved keyword, you shouldn't use it in your table column names.

If you want to keep this keyword, you can use this request :

SELECT * 
FROM mytable 
WHERE `FROM` = 'google'
AND email NOT IN (
    SELECT email 
    FROM mytable
    WHERE `FROM`='yahoo'
)

edited following comments

edited May 06 '13 at 12:19

answered May 06 '13 at 12:11

zessx

68,042
28
135
158

aside from syntax error. your query will not return as expected. – John Woo May 06 '13 at 12:12
Can you add in something like `AND email NOT IN (SELECT email FROM mytable WHERE \`FROM\`='yahoo')` to exclude all those with `yahoo` matches? – tadman May 06 '13 at 12:17
Sorry, obviously my was only an example. Edited table... But i need help :) – sineverba May 06 '13 at 12:18
how did this solve the problem? http://www.sqlfiddle.com/#!2/79a9b/3 – John Woo May 06 '13 at 12:53
it will display `text@example.net` in the result which exist on both `coming`. – John Woo May 06 '13 at 12:53
Wrong, we've got `test@example.net` and `text@example.com` (`s`/`x`) – zessx May 06 '13 at 13:51
@zessx how do you know it's a typo, oh yes if it was then why is `testing@foo.com` not in the result list? – John Woo May 06 '13 at 14:58
Should I really explain a third time ? Take your time, correct this typo in your head, and read a bit carefully. Everything is working correctly, I can repeat myself, but I don't think this'll help you... – zessx May 06 '13 at 15:02

score 2 · Answer 2 · answered May 06 '13 at 12:33

2

see how a non-normalized table generates a poor performance query just to satisfy your needs,

SELECT a.*
FROM   TableName a
       INNER JOIN
      (
          SELECT substr
          FROM
          (
              SELECT email,
                     REVERSE(SUBSTRING(REVERSE(SUBSTRING(email, LOCATE('@', email) + 1)),LOCATE('.', REVERSE(SUBSTRING(email, LOCATE('@', email) + 1)))+1)) substr,
                     coming
              FROM   tableName
          ) s
          GROUP BY substr
          HAVING COUNT(DISTINCT coming) = 1 AND
                 MAX(coming) IN ('yahoo','google')
      ) b ON a.email LIKE CONCAT('%@',b.substr,'.%')

OUTPUT

╔═════════════════╦════════╗
║      EMAIL      ║ COMING ║
╠═════════════════╬════════╣
║ info@domain.com ║ google ║
║ info@super.net  ║ google ║
╚═════════════════╩════════╝

answered May 06 '13 at 12:33

John Woo

258,903
69
498
492

It won't display `text@example.net` and `test@example.com` in the result which doesn't exist on both coming. – zessx May 06 '13 at 13:54
according from your statement: "*..I would extract ONLY value that are from "google" for example and not from "yahoo". So, in my SQL, i would read only: `info@domain.com` and `info@super.net` because the other are FROM google && yahoo..."* -- why should `example` domain be on list when they are from google and from yahoo? perhaps you can update your question to satisfy with your accepted answer. – John Woo May 06 '13 at 13:58
There's obviously a typo in the question, both should be `test@example.net` in the original example. You're just trying to solve a problem which has never been. – zessx May 06 '13 at 14:02
@zessx No, i'm just trying to help and i understood the question well. did you really understand the question? anywayy, your answer *seems* right because it's accepted `:)` – John Woo May 06 '13 at 14:16
@zessx how do you know it's a typo, oh yes if it was then why is `testing@foo.com` not in the result list? – John Woo May 06 '13 at 14:21

MySQL with multiple WHERE clauses

2 Answers2