Click here to Skip to main content
15,880,967 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
drop table if exists citydistance 
create table citydistance 
(
city1  varchar(100),
city2  varchar(100),
distanace int
)


insert into citydistance values ('Delhi','Mumbai',5000)
insert into citydistance values ('Mumbai','Delhi',5000)
insert into citydistance values ('Lucknow','Delhi',600)
insert into citydistance values ('Delhi','Lucknow',600)
insert into citydistance values ('Mumabi','Goa',200)
insert into citydistance values ('Goa','Mumabi',200)
insert into citydistance values ('Goa','Delhi',800)
insert into citydistance values ('Delhi','Goa',800)
insert into citydistance values ('Lucknow','Mumbai',800)
insert into citydistance values ('Mumbai','Lucknow',800)
insert into citydistance values ('Hydrabad','Lucknow',1800)


If you see in above table Delhi to Mumbai distance is 5000 while another entry of Mumbai to Delhi is also with 5000, so we want only one entry for Delhi to Mumbai with 5000 distance OR Mumbai To Delhi with 5000 distance, Any single entry in output is fine.
But in case of hydrabad to Lucknow, we have single entry, so single entry will come on output

Desired result set-

city1		city2		distanace
Delhi		Mumbai		5000
Lucknow		Delhi		600
Mumabi		Goa			200
Goa			Delhi		800
Lucknow		Mumbai		800
Hydrabad	Lucknow		1800


What I have tried:

I tried a lot but not able to think
Posted
Updated 1-Mar-22 0:27am

While we are more than willing to help those that are stuck, that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

If you are having problems getting started at all, then this may help: How to Write Code to Solve a Problem, A Beginner's Guide[^] - no, it's not SQL based, but trust me, it's the same process and principles for SQL as it is for C#!
 
Share this answer
 
You need to update the table twice.
First sort the table on City1 and City2 so ('Mumbai','Delhi',5000) becomes ('Delhi','Mumbai',5000).
Something similar to:
SQL
UPDATE citydistance
SET City1 = City2
   ,City2 = City1
WHERE City1 > City2

Then remove duplicates
 
Share this answer
 
Comments
Maciej Los 1-Mar-22 12:00pm    
5ed!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900