How do I get all states which are having common city by writing SQL query?

Question

0.00/5 (No votes)

See more:

Let say table having 2 columns i.e. State and City. Values are like:

State: S1, S2, S3
City: C1, C2, C1

So the output should be State: S1 & S3

What I have tried:

I tried self inner join but did not work.

Posted 8-Apr-21 18:22pm

mohit Gupta 2021

Updated 8-Apr-21 23:54pm

Add a Solution

Comments

Patrice T 9-Apr-21 0:27am

Show your code.

mohit Gupta 2021 9-Apr-21 0:44am

select State from TableState A
INNER JOIN TableState B
ON A.City = B.City

Patrice T 9-Apr-21 0:49am

Use Improve question to update your question.
So that everyone can pay attention to this information.

4 solutions

Solution 4

SQL

SELECT city,
STUFF((
SELECT ', ' + cast(state as varchar(max) )
FROM #temp
WHERE (city = a.city)
FOR XML PATH (''))
,1,2,'') AS state
FROM #temp a
GROUP BY city

Posted 8-Apr-21 23:53pm

Arfat M

Comments

CHill60 9-Apr-21 7:06am

Nicely formats the results but unfortunately does not cater for the 2 cities of same name in same state scenario

Arfat M 9-Apr-21 7:45am

Thank you.... To avoid duplicates ->SELECT distinct ', ' + cast(state as varchar(max) )

CHill60 9-Apr-21 9:22am

Not quite. If you consider this sample data

declare @TableState table ([state] nvarchar(50), [city] nvarchar(50));
insert into @TableState ([state],[city]) values('state1','city1'),('state1','city2'),('state1','city3'),
('state2','city1'),
('state3','city3'),
('state4','city4'),('state4','city4'),('state5','city1'),
('state5','city2');

then the expected results are

city1 - state1, state2, state5
city2 - state1, state5
city3 - state1, state3

but your code returns

city	state
city1	state1, state2, state5
city2	state1, state5
city3	state1, state3
city4	state4

Arfat M 14-Apr-21 8:07am

Kindly check the below code its correct only:

SELECT city,
STUFF((
SELECT distinct  ', ' + cast(state as varchar(max) )
FROM #temp
WHERE (city = a.city)
FOR XML PATH (''))
,1,2,'') AS state
FROM #temp a
GROUP BY city

CHill60 16-Apr-21 10:49am

As per my comment above. Returns state4 from my sample data - this is where state4 has two cities of the same name, but the requirement is to list states have a common city.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2021-04-08T23:17:00

I may as well put my punt in here as well as your own code wasn't too far off from a solution. With a minor addition to the ON clause and the addition of DISTINCT you get the results you are after

SQL

select distinct a.state
from TableState a
inner join TableState b on a.city = b.city and a.[state] <> b.[state]

That and a.[state] <> b.[state] also handles the two cities with the same name in the same state scenario.

The fun starts when you also want to know which cities are in multiple states as you will then start to get results like

state	city	state	city
state1	city3	state3	city3
state3	city3	state1	city3

Both rows are stating the same thing. But that's not what you asked for so I'll not start digging too deep!

Richard Deeming · Accepted Answer · 2021-04-08T22:34:00

Solution 2

The simple approach should work:

SQL

SELECT DISTINCT
    State
FROM
    TableState As a
WHERE
    Exists
    (
        SELECT 1
        FROM TableState As b
        WHERE b.City = a.City
        And b.State != a.State
    )
;

Posted 8-Apr-21 22:34pm

Richard Deeming

Jörgen Andersson · Accepted Answer · 2021-04-08T20:39:00

First you need to find which City names exists more than once.

SQL

SELECT  City
FROM    TableState
GROUP BY City
HAVING COUNT(DISTINCT State) > 1

The distinct is needed in case you have the same city name more than once in a state.

Then join this as a subquery to find which states the cities belong to.