MySQL with multiple WHERE clauses

Question

I've a db with this info (example)

EMAIL                 COMING    
[email protected]       google
[email protected]      google
[email protected]      yahoo
[email protected]        google
[email protected]       yahoo
[email protected]       google

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

[email protected]

and

[email protected]

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 `[email protected]` in the result which exist on both `coming`. – John Woo May 06 '13 at 12:53
Wrong, we've got `[email protected]` and `[email protected]` (`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 `[email protected]` 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 ║
╠═════════════════╬════════╣
║ [email protected] ║ google ║
║ [email protected]  ║ google ║
╚═════════════════╩════════╝

answered May 06 '13 at 12:33

John Woo

258,903
69
498
492

It won't display `[email protected]` and `[email protected]` 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: `[email protected]` and `[email protected]` 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 `[email protected]` 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 `[email protected]` not in the result list? – John Woo May 06 '13 at 14:21

MySQL with multiple WHERE clauses

2 Answers2