Hossein Dahr Hossein Dahr - 1 month ago 9
SQL Question

How to join two columns in a table with a primary key in another table?

I have two tables named

BusCity
and
BusPath
which
BusCity
has the
cities
and
BusPath
stores the paths.
BusPath
has two columns
DepId
and
DesId
.
I want to join these tables and get name of the cities.

Here is my code sample:

with cte as (
select
BusPath.DepId,
BusCity.CityName as 'مبدا'
from BusPath
inner join BusCity on BusCity.Id =BusPath.DesId
), ctf as(
select
BusPath.DesId,
BusCity.CityName as 'مقصد'
from BusPath
inner join BusCity on BusCity.Id =BusPath.DepId)
select * from cte , ctf

Answer

From this comment

In fact i want to have distinct join for each columns

I understand that your problem is that you have duplicates. It's because you make a cartesian product between cte and ctf

But I don't see why you make 2 CTE for just getting your city names...

Isn't simply that you are trying to do ?

select
    P.DesId, Des.CityName as 'مقصد',
    P.DepId, Dep.CityName as 'مبدا'
from BusPath P
inner join BusCity Dep on Dep.Id = P.DepId
inner join BusCity Des on Des.Id = P.DesId