Hi everybody, I'm trying to create a new data table using the example below (the table contains tousands of entry so this is just part of it):
City Language code
Paris French 2
New York English 3
Delhi English 3
Berlin German 5
Marseille French 2
Hamburg German 5
Munich German 6
the ouput should be something like this:
Source Destination Language code
Paris Marseille French 2
NY Delhi English 3
Berlin Hamburg German 5
The purpose is to have a source X, destination Y based on same language and same code without having source Y, destination X in the table.
Please advise on how to achieve it.
Hi Harish, thank you so much for your reply. Is your assumption important in the sql statement, because i have actually many rows with same language and same code, and thus output should be multiple source X destination Y without source Y destination X
The input: for same Code C and Language L we have cities X, Y , Z for example, then cities output should be X Y / X Z / Z Y. The order source, destination doesn t matter (X Y or Y X)
is it dynamic
.. means for 1 code and language you could have 3 records
..............for another code and language you could have 5 records
is this how it would be !!
Hi, thanks for ur feedback . Yes it s dynamic you can have different scenarios and the ouput is what you stated also.
i have the SQL ..
here .. there is a case where there is only 1 row ..
my output is excluding that .. do we want that also ???
for code 6
SELECT DISTINCT b.city,
FROM data b,
WHERE a.city <> b.city
AND b.city < a.city
AND a.code = b.code
AND a.language = b.language
ORDER BY code,
Hi Harish, amazing!! No for one row it should be excluded like u said. Thank u so much!!