Hossein Dahr Hossein Dahr - 28 days ago 4
SQL Question

How to add join by with clause for two column in a 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
Comments