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
Hi Harish,
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)
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,
a.city,
b.code,
b.language
FROM data b,
data a
WHERE a.city <> b.city
AND b.city < a.city
AND a.code = b.code
AND a.language = b.language
ORDER BY code,
language