CODEWITHSUNDEEP
mysql
Chris Rockwell
Chris Rockwell

Reputation: 1842

Select when value is not present in either of 2 columns

I have two fields, email and addlEmail. I need a select that only selects the record if a given email address is not in either field.

Attempt at using NOT IN with two columns (returns: Cardinality violation: 1241 Operand should contain 2 column(s):)

SELECT WebUsername, 
  WebPassword, 
  Active, 
  Email, 
  AddlEmail, 
  ShowYear 
FROM Exhibitors e 
WHERE e.Active = '-1' 
AND e.ShowYear = 2013 
AND (e.Email, e.AddlEmail) NOT IN ('[email protected]', '[email protected]')"

I have tried AND and OR, they do not work for obvious reasons.

For the sake of trying to be thorough:

$emails = "'[email protected]', '[email protected]'";
// example table data for 2 fields in question
Row | Email              |   AddlEmail
1   | [email protected]    |   [email protected]
2   | [email protected]   |   [email protected]
3   | [email protected]   |   null
4   | [email protected]   |   [email protected]

The query should only return rows 3 and 4.

Thanks for taking a look, and please let me know if you need further clarification.

Chris

EDIT: Sample table in response to answer posted:

CREATE TABLE `doubleSelect` (
  `Email` varchar(255) DEFAULT NULL,
  `AddlEmail` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `doubleSelect` (`Email`, `AddlEmail`) VALUES
  ('[email protected]', '[email protected]'),
  ('[email protected]', '[email protected]'),
  ('[email protected]', '[email protected]'),
  ('[email protected]', '[email protected]'),
  (NULL, '[email protected]'),
  ('[email protected]', NULL);

Sample Query (does not return the last row, and it should):

SELECT *
  FROM `doubleSelect`
  WHERE Email NOT
  IN (
    '[email protected]'
  )
  AND AddlEmail NOT
  IN (
    '[email protected]'
  )

Upvotes: 3

Views: 1413

Answers (2)

vinodhrajagopal
vinodhrajagopal

Reputation: 153

Try this 

select * 
from Exhibitors 
where (email is null or email not in('[email protected]', '[email protected]'))  
and   (addlEmail is null or addlEmail not in ('[email protected]', '[email protected]'))

Upvotes: 2

Jirka Kopřiva
Jirka Kopřiva

Reputation: 3099

simply 

AND e.Email NOT IN ('[email protected]', '[email protected]') 
AND e.AddlEmail NOT IN ('[email protected]', '[email protected]')

including NULL values:

SELECT *
FROM `doubleSelect`
WHERE (Email IS NULL or Email NOT IN ('[email protected]'))
      AND (AddlEmail IS NULL or AddlEmail NOT IN ('[email protected]'))

Upvotes: 7

Related Questions