CODEWITHSUNDEEP
mysqlsql
user3650825
user3650825

Reputation: 13

SQL Select First Name and Doesn't Contain Last Name Matching

I have a table with FirstName and LastName.

FirstName   LastName
John        Smith
John        Taylor
Steve       White
Adam        Scott
Jane        Smith
Jane        Brown

I want to select LastName that does not contain "Smith". If it matchs, don't use any of the same FirstName

Output Result

FirstName   LastName
Steve       White
Adam        Scott

Notice "John Taylor" and "Jane Brown" aren't in the result, because the other John and Jane name contain Smith.

My current query (includes John Taylor and Jane Brown):

Select FirstName, LastName
From tablPerson
where LastName != "Smith"

Upvotes: 0

Views: 5145

Answers (4)

Strawberry
Strawberry

Reputation: 33935

And just for good measure... with those strains from Dvořák's New World Symphony playing in the background

SELECT x.* 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.firstname = x.firstname 
   AND y.lastname = 'smith' 
 WHERE y.firstname IS NULL;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can do this with not exists:

select p.*
from tablPerson p
where not exists (select 1
                  from tablPerson p2
                  where p2.LastName = 'Smith' and p2.FirstName = p.FirstName
                 );

I prefer not exists for this type of query because it has more intuitive behavior if any first names are NULL. If any of the first names for 'Smith' are NULL, then not in returns the empty set.

Upvotes: 0

Simon Ludwig
Simon Ludwig

Reputation: 1804

Select * from Name
where firstName not in 
(Select firstName from Name where lastName like '%Smith%')

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30809

You need to use a sub query to filter out all the persons whose last name contains Smith, as mentioned below:

select first_name, last_name from person 
where first_name not in ( 
select first_name from person where last_name like '%Smith%');

Here's the example SQL Fiddle.

Upvotes: 0

Related Questions