SQL select addresses from a state in single query from 2 tables

Question

I don't know if the title makes any sense, but here is the situation: See 2 tables below

People: (BTW, if it helps to know, this table will never have >1000 rows)

+----+---------+-------------------+---------+
| id |  name   |      address      | city_id |
+----+---------+-------------------+---------+
|  1 | person1 | some address      |     123 |
|  2 | person2 | another address   |     542 |
|  3 | person3 | different address |     623 |
+----+---------+-------------------+---------+

Cities: (this one may contain all cities with states(and addl. column for country) around the globe)

+-----+-------+--------+
| id  | city  | state  |
+-----+-------+--------+
| 123 | city1 | state1 |
| 542 | city2 | state1 |
| 623 | city3 | state2 |
+-----+-------+--------+

To start, I know only people.id. Using this I need to find all people that belong to same state (not same city). For example, if I have people.id=1, I need to get all people from the state that person1 (people.id = 1) belongs to:

Output:

+----+---------+-----------------+---------+
| id |  name   |     address     | city_id |
+----+---------+-----------------+---------+
|  1 | person1 | some address    |     123 |     /*Both the people are from state1*/
|  2 | person2 | another address |     542 |
+----+---------+-----------------+---------+

I'm able to achieve this in two queries: A variable $state storing output of

SELECT c.state from people p INNER JOIN cities c ON p.city_id=c.id where p.id=<my input>;

and then another query

SELECT a.* FROMpeoplea INNER JOINcitiesb ON a.city_id=b.id WHERE b.state=$state

Is there a more efficient way to achieve this with a single JOIN? I tried combining the two queries to SELECT with JOIN within a JOIN(in subquery) which doesn't feel right somehow.

P.S: I'm not looking for recommendations on normalization or other changes to schema. All that is already in consideration for another development branch for later upgrade.

Zafar Malik · Accepted Answer · 2015-10-08T08:48:48.173

2

You can try below query-

SELECT p2.*
FROM people p 
JOIN cities c ON p.city_id=c.id 
JOIN cities c2 ON c.state=c2.state  
JOIN people p2 ON p2.city_id=c2.id 
WHERE p.id=<my input>;

Note: For performance id and city_id in people table and id and state in cities table should be indexed.

Also for more optimization you should use state_id instead of state for join and for this you have to create state_id field in your table.

edited Oct 08 '15 at 08:48

answered Oct 08 '15 at 07:24

Zafar Malik

6,734
2
19
30

I think you misread my question. i need people that live in same state as ``... this will give me all people from same city. BTW, even the query in your answer can be performed with just a single join. 2nd join is not essential. – Fr0zenFyr Oct 08 '15 at 08:01
@Fr0zenFyr `2nd join is not essential` - I think you've misread the answer. This _doesn't_ do what you want but it's not helpful to ask a question and comment on each answer "I've already tried/thought of that" - it indicates your question can be clearer that you're getting answers you've already tried or miss the point. – AD7six Oct 08 '15 at 08:03
@AD7six: thanks for pointing out. Highlighted the specific requirement in question. – Fr0zenFyr Oct 08 '15 at 08:17
the edit doesn't really help - **putting everything in bold makes it harder to read** what would help is being more concise and simply showing what you've already tried (not describing, *showing*, and saying why what you've tried didn't work/isn't what you want). No need to reply to me individually, the advice is there for the taking, or not =). – AD7six Oct 08 '15 at 08:19
The updated query does work. I already have the indexes you suggested. About changing the schema, I can't afford it as it is but I will consider that for another branch I'm working on where the major focus is on improvement of performance. Thanks. BTW, I don't know which approach is more efficient - your solution or my existing approach of doing two separate queries. In a quick test, both did almost equally. I don't have huge dataset in reality to test this. I'll accept this solution if I don't get a better one by evening. Cheers! – Fr0zenFyr Oct 08 '15 at 09:01

score 2 · Answer 2 · answered Oct 08 '15 at 07:38

2

You might try this.

select * from people
where city_id in(
    select city from cities c
    inner join(
        select c.state from people p
        left join cities c on c.city = p.city_id
        where p.id = '1'
    ) s on s.state = c.state
)

answered Oct 08 '15 at 07:38

Hotdin Gurning

1,821
3
15
24

Well, this looks like an overkill... pretty much similar to what I wanted to avoid... already mentioned in my question. – Fr0zenFyr Oct 08 '15 at 07:49
1

@Fr0zenFyr And you also mention this `this table will never have >1000 rows` – Hotdin Gurning Oct 08 '15 at 07:51
But the `cities` table may have 100,000 rows or even more... BTW I already know of this solution as noted in question, I'm looking for an optimized query which doesn't have to do a Join within a JOIN – Fr0zenFyr Oct 08 '15 at 07:56

score 1 · Answer 3 · edited Oct 08 '15 at 09:47

1

SELECT * 
FROM people p1 
WHERE p1.city_id in (
    SELECT c1.id 
    FROM cities c1 
    WHERE c1.state IN (
        SELECT c2.state 
        FROM people p2,
             cities c2 
        WHERE c2.id = p2.city_id 
          AND p2.id = 1
        )
    );

Instead of p2.id = 1 in the query, give the id of person from people table to whom you want to get the data.

edited Oct 08 '15 at 09:47

jarlh

42,561
8
45
63

answered Oct 08 '15 at 09:13

rks

9
4

i thought using `in()` is not a very good idea for performance. Surprisingly, `Explain` shows same plan for [Zafar Malik's answer](http://stackoverflow.com/a/33009238/1369473) which makes me believe that the query is re-written by optimizer to follow similar execution plan. Further research lead me to [this question](http://stackoverflow.com/questions/3417074/why-would-an-in-condition-be-slower-than-in-sql) which clarifies a lot of things – Fr0zenFyr Oct 08 '15 at 09:33

admlange · Answer 4 · 2015-10-08T07:24:51.590

0

Select a.* from 
people as a
inner join (
select cityid from people where personid = <my input>
) as b on b.cityid = a.cityid

-- or --

WITH CTE as
(
    select cityid from people where personid = <my input>
)

select a.* from people as a
inner join CTE as b on b.cityid = a.cityid;

edited Oct 08 '15 at 07:24

answered Oct 08 '15 at 07:18

admlange

31
2

Doesn't this just fetch the people from same city, if at all? I need people within same state as input person `id` – Fr0zenFyr Oct 08 '15 at 07:53

SQL select addresses from a state in single query from 2 tables

4 Answers4