StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL concatenation when there are null fields
text
Body
copied!<p>I created a VIEW using this code:</p> <pre><code>CREATE OR REPLACE VIEW aaa AS SELECT pry.uid, treg.nombre_es as region, tpais.nombre_es as pais, tdep.departamento, dep_other, tciu.ciudad, ciu_other FROM tx_oriproyectos_proyectos AS pry LEFT JOIN tx_oritablascomunes_regiones as treg ON pry.region = treg.uid LEFT JOIN tx_oritablascomunes_paises as tpais ON pry.pais = tpais.uid LEFT JOIN tx_oritablascomunes_departamentos as tdep ON pry.departamento = tdep.uid LEFT JOIN tx_oritablascomunes_ciudades as tciu ON pry.ciudad = tciu.uid </code></pre> <p>And I obtained this and is Ok:</p> <p><a href="http://finewebdesigns.com/images/mysql_view_result.jpg" rel="nofollow noreferrer">result http://finewebdesigns.com/images/mysql_view_result.jpg</a></p> <p>And now I need to obtain concatenate results like this:</p> <pre><code>concatenated_field --------------------------------------- Africa - ALbania - Tirana1 - Tirana2 Africa - Colombia - Guaviare - Calamar </code></pre> <p>How can I do that?</p> <hr> <p>I tried this:</p> <pre><code>CREATE OR REPLACE VIEW aaa AS SELECT CONCAT_WS (' - ', pry.uid, treg.nombre_es as region, tpais.nombre_es as pais, tdep.departamento, dep_other, tciu.ciudad, ciu_other) FROM tx_oriproyectos_proyectos AS pry LEFT JOIN tx_oritablascomunes_regiones as treg ON pry.region=treg.uid LEFT JOIN tx_oritablascomunes_paises as tpais ON pry.pais=tpais.uid LEFT JOIN tx_oritablascomunes_departamentos as tdep ON pry.departamento=tdep.uid LEFT JOIN tx_oritablascomunes_ciudades as tciu ON pry.ciudad=tciu.uid </code></pre> <p>But I obtained: </p> <pre><code>#1583 - Incorrect parameters in the call to native function 'CONCAT_WS' </code></pre> <hr> <p>Ok, Thanks to @Mat I finally get this code, that is the expected solution to this problem.</p> <pre><code>CREATE OR REPLACE VIEW aaa AS SELECT pry.uid, CONCAT_WS (' - ', treg.nombre_es, tpais.nombre_es, tdep.departamento, NULLIF(dep_other,''), tciu.ciudad, NULLIF(ciu_other,'')) FROM tx_oriproyectos_proyectos AS pry LEFT JOIN tx_oritablascomunes_regiones as treg ON pry.region=treg.uid LEFT JOIN tx_oritablascomunes_paises as tpais ON pry.pais=tpais.uid LEFT JOIN tx_oritablascomunes_departamentos as tdep ON pry.departamento=tdep.uid LEFT JOIN tx_oritablascomunes_ciudades as tciu ON pry.ciudad=tciu.uid </code></pre> <p>That obtains this: <a href="http://finewebdesigns.com/images/mysql_view_result_solved.jpg" rel="nofollow noreferrer">http://finewebdesigns.com/images/mysql_view_result_solved.jpg</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload