I am working on the complex MySQL query with subquery and JOINs and this is my query:
SELECT
id,
ancient_source_name,
ancient_source_name_alt,
ancient_source_type,
ancient_source_type_id,
IFNULL(IF(ancient_source_material = 'Unknown', NULL, ancient_source_material), '-') AS ancient_source_material,
ancient_source_year,
dynasty,
ancient_period,
author,
IFNULL(CONCAT_WS(', ',
IF(provenance_country = 'Unknown' OR provenance_country = 'Not Applicable', NULL, provenance_country),
IF(provenance_locality = 'Unknown' OR provenance_locality = 'Not Applicable', NULL, provenance_locality),
IF(provenance_place = 'Unknown' OR provenance_place = 'Not Applicable', NULL, provenance_place)
), '-') AS provenance_loc,
IFNULL(CONCAT_WS(', ',
IF(current_country = 'Unknown' OR current_country = 'Not Applicable', NULL, current_country),
IF(current_locality = 'Unknown' OR current_locality = 'Not Applicable', NULL, current_locality),
IF(current_place = 'Unknown' OR current_place = 'Not Applicable', NULL, current_place)
), '-') AS current_loc
FROM (
SELECT
hgn_ancient_sources.id AS id,
ancient_source_name,
IFNULL(ancient_source_name_alt, '-') AS ancient_source_name_alt,
IFNULL(ancient_source_year, '-') AS ancient_source_year,
ancient_source_type,
ancient_source_type_id,
ancient_source_material,
dynasty,
ancient_period,
provenance_countries.country AS provenance_country,
provenance_localities.locality AS provenance_locality,
provenance_places.place AS provenance_place,
current_countries.country AS current_country,
current_localities.locality AS current_locality,
current_places.place AS current_place,
IFNULL(author_name, '-') AS author
FROM hgn_ancient_sources
JOIN hgn_ancient_source_types ON hgn_ancient_sources.ancient_source_type_id = hgn_ancient_source_types.id
JOIN hgn_ancient_source_materials ON hgn_ancient_sources.ancient_source_material_id = hgn_ancient_source_materials.id
JOIN hgn_dynasties ON hgn_ancient_sources.ancient_source_dynasty_id = hgn_dynasties.id
JOIN hgn_ancient_periods ON hgn_ancient_sources.ancient_source_period_id = hgn_ancient_periods.id
LEFT JOIN junc_ancient_source_has_author ON hgn_ancient_sources.id = junc_ancient_source_has_author.ancient_source_id
LEFT JOIN hgn_authors ON junc_ancient_source_has_author.author_id = hgn_authors.id
JOIN junc_place_has_location AS provenance_place_has_location ON hgn_ancient_sources.ancient_source_provenance_place_id = provenance_place_has_location.id
JOIN junc_place_has_location AS current_place_has_location ON hgn_ancient_sources.ancient_source_current_place_id = current_place_has_location.id
JOIN junc_locality_has_country AS provenance_locality_has_country ON provenance_place_has_location.location_id = provenance_locality_has_country.id
JOIN junc_locality_has_country AS current_locality_has_country ON current_place_has_location.location_id = current_locality_has_country.id
JOIN hgn_places AS provenance_places ON provenance_place_has_location.place_id = provenance_places.id
JOIN hgn_places AS current_places ON current_place_has_location.place_id = current_places.id
JOIN hgn_localities AS provenance_localities ON provenance_locality_has_country.locality_id = provenance_localities.id
JOIN hgn_localities AS current_localities ON current_locality_has_country.locality_id = current_localities.id
JOIN hgn_countries AS provenance_countries ON provenance_locality_has_country.country_id = provenance_countries.id
JOIN hgn_countries AS current_countries ON current_locality_has_country.country_id = current_countries.id
) AS subquery
Everything works fine except this part of code:
IFNULL(CONCAT_WS(', ',
IF(provenance_country = 'Unknown' OR provenance_country = 'Not Applicable', NULL, provenance_country),
IF(provenance_locality = 'Unknown' OR provenance_locality = 'Not Applicable', NULL, provenance_locality),
IF(provenance_place = 'Unknown' OR provenance_place = 'Not Applicable', NULL, provenance_place)
), '-') AS provenance_loc,
IFNULL(CONCAT_WS(', ',
IF(current_country = 'Unknown' OR current_country = 'Not Applicable', NULL, current_country),
IF(current_locality = 'Unknown' OR current_locality = 'Not Applicable', NULL, current_locality),
IF(current_place = 'Unknown' OR current_place = 'Not Applicable', NULL, current_place)
), '-') AS current_loc
I am getting good results when there are 1/3 or 2/3 columns with Unknown or Not Applicable value, but when 3/3 columns (e.g. current_country, current_locality, current_place) has one of these two values, I get empty cell as a result, and not -, as should be expected.
I tried different changes in the code, and nothing works. I should mention that, for example, this part of the query:
IFNULL(IF(ancient_source_material = 'Unknown', NULL, ancient_source_material), '-') AS ancient_source_material
work perfectly, so my assumption is that there is some problem with CONCAT_WS() function, but I cannot find what.
CONCAT_WS() returns an empty string if all arguments after a non-null separator are NULL.
See the demo.
I did not find this stated explicitly in MySql's documentation (like it is made clear in SQL Server's documentation).
What you can do is use also NULLIF() to check if the result is an empty string and change it to NULL before you use IFNULL():
IFNULL(NULLIF(CONCAT_WS(', ',
IF(provenance_country = 'Unknown' OR provenance_country = 'Not Applicable', NULL, provenance_country),
IF(provenance_locality = 'Unknown' OR provenance_locality = 'Not Applicable', NULL, provenance_locality),
IF(provenance_place = 'Unknown' OR provenance_place = 'Not Applicable', NULL, provenance_place)
), ''), '-') AS provenance_loc
See the demo.