i got a CSV file from the post of swizerland with all addresses. For the usecase we only need about 10% of the includet data. I try now to merge and cut out not needet data. Swizerland uses 3 different languages for some citys / street-names. The main-table includes always the spoken language from the city. In a second table there is one row for the second, and a second row for the third language.
At the end I want something like this as output:
PID | ZIP_CODE | DE_CITY | FR_CITY | IT_CITY
My query looks like that:
SELECT
NEW_PLZ1.ONRP,
case
when NEW_PLZ1.SPRACHCODE = '1' then NEW_PLZ1.ORTBEZ18
when NEW_PLZ2.SPRACHCODE = '1' then NEW_PLZ2.ORTBEZ18
end as 'DE_CITY',
case
when NEW_PLZ1.SPRACHCODE = '2' then NEW_PLZ1.ORTBEZ18
when NEW_PLZ2.SPRACHCODE = '2' then NEW_PLZ2.ORTBEZ18
end as 'FR_CITY',
case
when NEW_PLZ1.SPRACHCODE = '3' then NEW_PLZ1.ORTBEZ18
when NEW_PLZ2.SPRACHCODE = '3' then NEW_PLZ2.ORTBEZ18
end as 'IT_CITY'
FROM
NEW_PLZ1
LEFT JOIN NEW_PLZ2 ON NEW_PLZ1.ONRP = NEW_PLZ2.ONRP;
ONRP is the primary key and FK in both tables. SPRACHCODE is 1 for german, 2 for french, 3 for italian.
With the query above I get the result I wish - but one language is always a seperate row (because of the join table with 2 rows)
I tried then use GROUP BY ONRP but getting the following error:
[42000][1055] Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'adressen.NEW_PLZ2.SPRACHCODE' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
I found on stackoverflow following: -disable strict mode: this worked to execute the query. but the result is not good. (missing data, wrong data) -add all used fields in GROUP BY this worked also to execute query. But also with missing data or wrong data.
Atm my creativity is broken to try out how to solve that. I dont know what else keywords i can search here which would help me.
I'm happy for advice to: resolve my brainbreaker for the group-by and why its not working or how to do the query for wished output in other way.
important: the query will not be in production env. It will be used only to create a new database/table with the needet data.
Thanks a lot for help
Use an Aggregation function
Of course you must test different functions till you get your result
SELECT
NEW_PLZ1.ONRP,
MAX(case
when NEW_PLZ1.SPRACHCODE = '1' then NEW_PLZ1.ORTBEZ18
when NEW_PLZ2.SPRACHCODE = '1' then NEW_PLZ2.ORTBEZ18
end) as 'DE_CITY',
MAX(case
when NEW_PLZ1.SPRACHCODE = '2' then NEW_PLZ1.ORTBEZ18
when NEW_PLZ2.SPRACHCODE = '2' then NEW_PLZ2.ORTBEZ18
end) as 'FR_CITY',
MAX(case
when NEW_PLZ1.SPRACHCODE = '3' then NEW_PLZ1.ORTBEZ18
when NEW_PLZ2.SPRACHCODE = '3' then NEW_PLZ2.ORTBEZ18
end) as 'IT_CITY'
FROM
NEW_PLZ1
LEFT JOIN NEW_PLZ2 ON NEW_PLZ1.ONRP = NEW_PLZ2.ONRP
GROUP BY NEW_PLZ1.ONRP;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments