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 (
BusCity.CityName as 'مبدا'
inner join BusCity on BusCity.Id =BusPath.DesId
), ctf as(
BusCity.CityName as 'مقصد'
inner join BusCity on BusCity.Id =BusPath.DepId)
select * from cte , ctf
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
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