Using a table to get results from another

[clay1]

I've got a table with a list of cities, and a table with people. The cities table has a column for a manager

 

I want all the people from the cities managed by a certain person

 

Such as Select * from cities where manager = 'joe'

 

Then use that to select * from people where city = all the cities from the other query

 

Can I do this in one step or do I need to select the cities, put them in an array and loop through them while selecting the people?

 

I'm using PG but any general help is welcome

[Psycho]

SELECT *
FROM people
WHERE city = IN ( SELECT city FROM cities WHERE manager = 'joe' )

OR

SELECT people.*
FROM people
JOIN city ON people.city = city.city
WHERE cities.manager = 'joe'

[clay1]

This seemed to work!

 

Thank you-- an if you wouldn't mind could you explain the code a bit?

[Psycho]

Using IN is like a multiple WHERE clauses. Example:

SELECT *
FROM users
WHERE favoriteColor IN ('blue', 'green', 'red')

That is logically the same as

SELECT *
FROM users
WHERE favoriteColor ==  'blue'
  OR favoriteColor ==  'green'
  OR favoriteColor ==  'red'

 

In addition to using a comma separated list of values for the IN values you can also do a query. So, I used a query to get all the city values for the selected managers. The result is a query that grabs all the people records where their city is in the list of cities for the selected manager.

 

The second query uses a simple JOIN. If you don't know how to use JOINs you need to learn. Without using JOINs you will be increasing your coding work and - most likely - the system overhead.