MySQL Query bring back those not in a list

Question

EDIT: What I need to show are those emails from the list in the SQL query that DO NOT appear in tresp.

I have the following code:

SELECT * FROM tresp WHERE email IN ('[email addy here]','[email addy here]','
[email addy here]','[email addy here]','[email addy here]')

This may sound odd but want I want to do is actually show the email addresses that are NOT in the tresp - is that possible?

Cheers,

H.

Do you mean you want to show emails from tresp that aren't in the list or emails in the list that aren't in tresp? — Joachim Isaksson, Feb 16 '12 at 19:37

score 5 · Answer 1 · answered Feb 16 '12 at 19:35

5

So... you want NOT IN then?

SELECT *  
FROM tresp  
WHERE email NOT IN ('[email addy here]','[email addy here]')

Or do you want the email addresses?

SELECT DISTINCT email 
FROM tresp 
WHERE email NOT IN ('[email addy here]','[email addy here]')

answered Feb 16 '12 at 19:35

josh.trow

4,861
20
31

Thanks but neither of those give what I need. I need to know from the list of email in the SQL statement, which of those are NOT in tresp - make sense? – Homer_J Feb 16 '12 at 19:42
You have a list of emails, you are querying tresp, you want to know which email addresses are in that list but are not in tresp? – Simon at The Access Group Feb 16 '12 at 19:58
@Simonatmso.net: Sounds like what he wants to me – josh.trow Feb 16 '12 at 20:00
@Homer_J: See [this SO Question](http://stackoverflow.com/questions/273623/mysql-select-from-a-list-of-numbers-those-without-a-counterpart-in-the-id-fiel) for more info and options – josh.trow Feb 16 '12 at 20:12
@josh.trow - Hmm, just read that - I take it I need a temp table? – Homer_J Feb 16 '12 at 20:22

Joachim Isaksson · Accepted Answer · 2012-02-17T23:22:31.047

3

Tricky question, this solution isn't very elegant but it works;

SELECT * FROM (
  SELECT 'email1' AS email UNION 
  SELECT 'email2'          UNION 
  SELECT 'email3'          UNION 
  SELECT 'email4'          UNION 
  SELECT 'email5' 
) AS a WHERE a.email NOT IN (SELECT email FROM tresp);

If you run into the same charset problems I had, create the table with default charset utf-8.

Edit: Added an AS since older MySQL seems to have trouble without it.

Demo at SQLFiddle.

edited Feb 17 '12 at 23:22

answered Feb 16 '12 at 20:04

Joachim Isaksson

176,943
25
281
294

This only works if you can pre-process your 'email' list and know its length, but in that case it would work - and maybe it's the only way – josh.trow Feb 16 '12 at 20:05
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') a WHERE a.email NOT IN (SELECT email FROM tresp)' at line 227 – Homer_J Feb 16 '12 at 20:09
@josh.trow I assume you'd generate the `SELECT xx UNION' part the same way as you'd generate the entries for IN, generating it in code from a list. And no, I can't really think of another way if the list needs to be a part of the query and not in a table. – Joachim Isaksson Feb 16 '12 at 20:11
@Homer_J I don't get an error when just copy/pasting it in. What MySQL version are you using? – Joachim Isaksson Feb 16 '12 at 20:12
Gotta love when someone else posts the same answer while writing it out! I coujldn't think of a more elegant way to do it either to be honest. Was torn between a left join or subquery for checking tresp – Simon at The Access Group Feb 16 '12 at 20:14

Simon at The Access Group · Answer 3 · 2012-02-16T20:38:42.643

0

It's not pretty but this may do what you wish, trying to find a more elegant solution but this at least works

SELECT 
    findTheseEmails.email
FROM
(
    SELECT CONVERT(_latin1 'test@foo.com' USING utf8) AS email
    UNION SELECT CONVERT(_latin1 'bar@fooo.com' USING utf8)
    UNION SELECT CONVERT(_latin1 'woww@foo.com' USING utf8)
    UNION SELECT CONVERT(_latin1 'teasfsafst@foo.com' USING utf8)
) AS findTheseEmails

LEFT JOIN tresp
ON tresp.email = findTheseEmails.email

WHERE tresp.email IS NULL

edited Feb 16 '12 at 20:38

answered Feb 16 '12 at 20:12

Simon at The Access Group

6,932
22
45

Error: #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_unicode_ci,COERCIBLE) for operation '=' – Homer_J Feb 16 '12 at 20:16
@Homer_J See the link in my answer for an answer to that problem. – Joachim Isaksson Feb 16 '12 at 20:33
@Homer_J adjusted the query - though personally I try and avoid mixing collations - would point @ something slightly amiss within your configuration – Simon at The Access Group Feb 16 '12 at 20:39
@Simonatmso.net: latin1_swedish_ci is the default collation for MySQL - that's what you get for having Swedish developers :) – josh.trow Feb 16 '12 at 20:47
@josh.trow Unfortunately I've had the pain of having to correct an old database schema from latin_1_swedish_ci to a more general collation during a migration. The "fun" hasn't fled memory just yet. Especially the ENUM fields. Suffice to say, a script to automate the process was rather required :) – Simon at The Access Group Feb 16 '12 at 20:59

MySQL Query bring back those not in a list

3 Answers3