[SOLVED] SELECT WHERE NOT IN

[soycharliente]

I have a table full of user profiles. One of the fields is `lastname` and I'm trying to select all the rows where the last name is not found in this string. I have a list of 485 names and have 492 profiles. I'm trying to figure out who I missed on this list without just going through it by hand.

 

SELECT * FROM `profiles` WHERE `lastname` NOT IN ('big string with a bunch of names that are space delimited')

 

I'm just getting every single row in my table back.

 

Any ideas about what's wrong with the query?

[fenway]

IN() works with comma delimited strings.

[fnairb]

The NOT IN is expecting a list of values and you are providing one.

 

Try splitting the "big string" into a list:

<?php
$nameList = preg_split('/\s+/', 'big string with a bunch of names that are space delimited', NULL, PREG_SPLIT_NO_EMPTY);
$nameIn = "'" . implode("', '", $nameList) . "'";

$query = "SELECT * FROM `profiles` WHERE `lastname` NOT IN ($nameIn)";

?>

[soycharliente]

So I did it with comma delimited strings and I'm still getting back every result in the database.

 

SELECT * FROM `profiles` WHERE `lastname` NOT IN ('name1','name2','name3')

[soycharliente]

Is it trying to match the entire string? Or just part of it?

[fenway]

So I did it with comma delimited strings and I'm still getting back every result in the database.

 

SELECT * FROM `profiles` WHERE `lastname` NOT IN ('name1','name2','name3')

Include ones with those lastnames?

[soycharliente]

So I did it with comma delimited strings and I'm still getting back every result in the database.

 

SELECT * FROM `profiles` WHERE `lastname` NOT IN ('name1','name2','name3')

Include ones with those lastnames?

 

I don't understand your question.

[fnairb]

Either I am missing your question or you may be miss understanding what NOT IN is doing.  I truly don't know which so I'm just going to throw out a simple® example to hopefully get on the same page.

 

SELECT * FROM `profiles` WHERE `lastname` NOT IN ('name1')

Is functionally identical to

SELECT * FROM `profiles` WHERE `lastname` != 'name1'

 

[soycharliente]

I am telling you that I run this code...

 

SELECT * FROM `profiles` WHERE `lastname` NOT IN ('John Doe','John Smith','Charlie Holder')

 

For this example table...

 

PROFILES ( with firstname and lastname fields)

-------------

John Doe

John Smith

Charlie Holder

 

and I get every row back when I should get none.

[soycharliente]

And I don't understand how having commas between all the names is any different than just putting all the names in one string. It's just a 1 element list.

[fnairb]

Shouldn't the list be just last names?

 

SELECT * FROM `profiles` WHERE `lastname` NOT IN (Doe','Smith','Holder')

[fenway]

And I don't understand how having commas between all the names is any different than just putting all the names in one string. It's just a 1 element list.

MySQL doesn't have "lists".

 

Shouldn't the list be just last names?

Yes, that too....

[soycharliente]

Is it trying to match the entire string? Or just part of it?

 

That's what I was asking. Is it matching exactly or just partially.

I guess exactly. When I do just the last names, it works, but with full names it doesn't. My list was full names and I was hoping to search the database and return all the rows where the lastname wasn't found in the string.

 

Guess we're SOLVED.

[fenway]

MySQL can do wildcard searches... but it's not appropriate here.

[fnairb]

I don't mean to beat a dead horse here and I am not meaning to speak down to you.  (I am truly sorry if it seems that way.) I think I see where you are coming from charlieholder.

 

The "IN" or in the case "NOT IN" is referring to the list not the values in the list.  To translate the query in to (near) English

 

 

SELECT * FROM `profiles` WHERE `lastname` NOT IN ('name1','name2','name3')

SELECT everything form the table profiles WHERE the vale of the lastname field is NOT one of the values IN the list: 'name1', 'name2', and 'name3'

 

It is not:

SELECT everything form the table profiles WHERE the value of the lastname field is NOT IN the stings: 'name1', 'name2', or 'name3'

 

 

(Really not sure if I am making my point with the second translation.)

[soycharliente]

Yeah yeah.

 

I don't think you're talking down to me. It just seems like you're trying to make sure that I understand the concept so I don't have to ask this question again and could help someone answer it if I had the chance.

 

Your explanation makes sense and validates my thought that it checks each element in it's entirety. I was reading it as the second definition.

 

Thanks for the help.