Databases
StackOverflow2013
Postsdbo
POSelect when value is not present in either of 2 columns
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelect when value is not present in either of 2 columns
    text
    copied!<p>I have two fields, <code>email</code> and <code>addlEmail</code>. I need a select that only selects the record if a given email address is not in either field.</p> <p>Attempt at using NOT IN with two columns (returns: <code>Cardinality violation: 1241 Operand should contain 2 column(s):</code>)</p> <pre><code>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 ('test@test.com', 'test2@test.com')" </code></pre> <p>I have tried AND and OR, they do not work for obvious reasons.</p> <p>For the sake of trying to be thorough:</p> <pre><code>$emails = "'email@email.com', 'email2@email.com'"; // example table data for 2 fields in question Row | Email | AddlEmail 1 | email@email.com | email3@email.com 2 | email4@email.com | email2@email.com 3 | email5@email.com | null 4 | email6@email.com | email7@email.com </code></pre> <p>The query should only return rows 3 and 4.</p> <p>Thanks for taking a look, and please let me know if you need further clarification.</p> <p>Chris</p> <p>EDIT: Sample table in response to answer posted:</p> <pre><code>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@email.com', 'email2@email.com'), ('email2@email.com', 'email3@email.com'), ('email4@email.com', 'email5@email.com'), ('email6@email.com', 'email@email.com'), (NULL, 'email@email.com'), ('email2@email.com', NULL); </code></pre> <p>Sample Query (does not return the last row, and it should):</p> <pre><code>SELECT * FROM `doubleSelect` WHERE Email NOT IN ( 'email@email.com' ) AND AddlEmail NOT IN ( 'email@email.com' ) </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload