CODEWITHSUNDEEP
mysql
user3255843
user3255843

Reputation: 69

MySQL return rows where one value exists in another column

I'm sure this has been asked before, but I don't think I am searching for it properly. I have a table like this:

SITE        STATUS        NEXT_SITE
chicago     good          cleveland
pittsburgh  bad           philadelphia
cleveland   bad           columbus
columbus    good          pittsburgh
pittsburgh  bad           chicago

What I want to do is to select rows where NEXT_SITE actually exists somewhere in SITE. So my query would not return the second row because philadelphia does not exist anywhere in the SITE column.

Any help would be appreciated.

Upvotes: 0

Views: 5245

Answers (2)

shawtydubs
shawtydubs

Reputation: 64

You can join it to itself. By joining on next_site = site, you will only return the values where they match, and therefore only the values where next_site exists in site.

select distinct t1.next_site
from your_table t1
join your_table t2 on t1.next_site = t2.site;

Upvotes: 1

juergen d
juergen d

Reputation: 204766

select * from your_table
where next_site in 
(
  select distinct site from your_table
)

Upvotes: 3

Related Questions