self join after an inner join

Question

I am finding what cities have the same name in different states. The city name and state name are in seperate tables (cities and states) and can be inner joined over a seperate common column.

select c1.city, c1.state, c2.city, c2.state 
from cities 
inner join states on cities.commonid = states.commonid

After inner joining i need to self join to perform a function as follows

select c1.city, c1.state, c2.city, c2.state
from *joined table* c1 join
     *joined table* c2
     on c1.city = c2.city and c1.state <> c2.state

i am wondering how i can self join a table that is the result of another join in the same query output will be like this

+----------+-------+--------+--------+
| city1    | state1|city2   |state2  |
+----------+-------+--------+--------+
| x        | melb  | x      | syd    |
| y        | bris  | y      | ACT    |
+----------+-------+--------+--------+

Your query looks correct. What is the problem, and can you add sample data which explains what you want here? — Tim Biegeleisen, Mar 28 '21 at 08:19
Please supply sample data and output to demonstrate your problem [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) — Stu, Mar 28 '21 at 08:26

forpas · Accepted Answer · 2021-03-28T11:19:51.087

I assume that the table cities has a column like state_id that references a column state_id in the table states (change the names to the actual names of the columns).
First do a self join for cities with the conditions:

c1.city = c2.city AND c1.state_id < c2.state_id

The < operator makes sure that each pair of cities wil be returned only once.
Then join 2 copies of states, because each of them will be used to get the name of the state for each of the 2 cities:

SELECT c1.city city1, s1.state state1,
       c2.city city2, s2.state state2
FROM cities c1 
INNER JOIN cities c2 ON c1.city = c2.city AND c1.state_id < c2.state_id
INNER JOIN states s1 ON s1.state_id = c1.state_id
INNER JOIN states s2 ON s2.state_id = c2.state_id
ORDER BY city1

yep; the < operator just solved my problem ~ thanks – shitathakin Mar 28 '21 at 11:27 — shitathakin, Mar 28 '21 at 11:27

Pankaj_Dwivedi · Answer 2 · 2021-03-28T08:42:33.797

0

You can do select of your inner join query and give it alias.

Then it will become something like below...

Select c.city,c.state from
(Select City,state from cities inner join states where cities.id = states.id) as c

Now make a self join for c.

edited Mar 28 '21 at 08:42

answered Mar 28 '21 at 08:33

Pankaj_Dwivedi

565
4
15

Why not put in the whole solution (having updated the join)? – Peter Smith Mar 28 '21 at 08:46
attempting to self join c in this situation returns an error `relation "c" does not exist LINE 5: join c c2 on c.city = c2.city and c.state <> c2.state` – shitathakin Mar 28 '21 at 09:00
Need to see the full query; sample data (not just results) would be good too. – Peter Smith Mar 28 '21 at 10:27

score 0 · Answer 3 · answered Mar 28 '21 at 10:43

I would perform a pre-query of all cities that have more than one state. Then join to the states table to see which states they are encountered.

    select
          Duplicates.city,
          s.state
       from
          ( select c1.city
               from cities c1
               group by c1.city
               having count(*) > 1 ) Duplicates
             JOIN cities c2
                on Duplicates.city = c2.city
                JOIN states s
                   on c2.commonid = s.commonid
      order by
         Duplicates.city,
         s.state

By NOT trying to do cross-tab of just two city/states, you would get a single list. If one city name exists in 5 states, how would you plan on showing that. This way you would see all alphabetized.

score 0 · Answer 4 · answered Mar 28 '21 at 11:21

I would suggest a CTE:

with cs as (
      select c.name as city_name, s.name as state_name
      from cities c join
           states s
           on c.commonid = s.commonid
     )
select cs1.*, cs2.*
from cs cs1 join
     cs cs2
     on cs1.name = cs2.name and cs1.state <> cs2.state;

self join after an inner join

4 Answers4