MySQL - Is this possible in one query?

Question

I'm currently running the following query:

SELECT * from people WHERE id = 4;

on the following table:

id       name          state   age
-----------------------------------
1       tony jones     CA      22
2       Sue Smith      FL      50
3       Alex Guam      AL      44
4       tony jones     SC      32
5       tony jones     OH      12
6       alex guam      RI      33
7       tony Jones     CO      17

I'd like to also return a list of other states that a person by that name lives in.

So in my query example I have id 4 for "tony jones" - I should also receive an "other_states" list of CA,OH,CO.

Is it possible in the one query or do I need to do a separate select after the fact based on 'name' ?

Answer 1

Use a second select. You might be able to pull something off with temporary tables and a join, but it wouldn't make much sense to use a query like that to get the information you need.

Answer 2

SELECT p1.* from people p1
  JOIN people p2 ON p1.name = p2.name
 WHERE p2.id = 4

will return all rows matching given name.

Answer 3

This will give you what you are looking for:

select t.id,
       t.name,
       t.age,
       t.group_concat(distinct t.state order by t.state separator ', ') as other_states
from   the_table t
    inner join the_table u on t.name = u.name 
where  u.id = 4
group by t.id, t.name, t.age

Answer 4

One way would be to left join the table with itself, matching on the name. A better way would to separate the records of individual people (name and age) and place it in another table. Then make this table include the id number from the person table you just created. You could then join the two tables on the person record id, and only use one query.