How to get all the rows of a table who's column value matches the rows of a select query in MySql

Question

Say I have a table called "people". I want to select all of the people that have a certain value in column "town". Then I want to select all the rows in a different table which have a value that exists in the previous selection.

Answer 1

Two options, both involve a sub-query...

1. Use an EXISTS clause

   SELECT * FROM `a different table` dt
   WHERE EXISTS (
     SELECT 1 FROM people p
     WHERE p.town = 'a certain value'
     AND p.value = dt.value -- you didn't specify column names
   )
   

2. Use an IN clause

   SELECT * from `a different table` dt
   WHERE dt.value IN (
     SELECT p.value FROM people p
     WHERE p.town = 'a certain value'
   )
   

Personally, I'd go with the first option but depending on your table types and indexes, you should run some explain-plans to see which is more performant.