Hi,
I have the table for train journey col 1 - From destinationn col 2 - To Destination col C distance
now i want to write a query to ge the unique journeys that are available?
for example there will be rows for
From - London To Paris
From Paris To London,
basically both are same trip, so wanted to get just one in my result?
how do i write a query to get this
SELECT COUNT(*)/2
FROM
(
SELECT [From],[To] FROM YourTable
UNION
SELECT [To],[From] FROM YourTable
)s;
SELECT COUNT(*)
FROM YourTable y1
WHERE NOT EXISTS
(
SELECT * FROM yourTable y2
WHERE y2.[From] = y1.[To]
AND y2.[To] = y1.[From]
AND y1.[From] > y2.[From]
);
Because you want the actual rows and not just the count, the first query I posted would not work. The second query should.
The second query looks at each record and uses the NOT EXISTS clause to include that record only if there is no record which has the reverse (i.e, the From of the record matches the To of another record, and the To of the record matches the From of the second record).
If you just have those two conditions, the two records - London to Paris and Paris to London - will eliminate each other. But we want to keep one of those. So you have the additional check y1.[From] > y2.[From]. The From of one of the records has to be greater than the From of the other (string sort). So you eliminate one, and retain the other.
select distinct
case when [from]<[to] then [from] else [to] end as [from]
,case when [from]<[to] then [to] else [from] end as [to]
,distance
from yourtable