SQL - query multiplying records returned - Cartesian Product
Your cartesian product should be the product of # of rows in properly joined tables times the rows in tables that are not properly joined (i.e. don't have effective condition in the WHERE clause). For cases where specimen.snop_axis = 'M' morphology and specimen are propely joined but functions isn't, and when specimen.snop_axis &lt;&gt; 'M' functions and specimen are properly joined but morphology isn't. That's most likely why the resulting number of rows doesn't seem to be any direct product of rows in the tables.

To solve this I'd first try to join all the tables properly by replacing

AND if(specimen.snop_axis = 'M', morphology.morphology_code = specimen.snop_code, functions.functions_code = specimen.snop_code) 


with

AND functions.functions_code = specimen.snop_code
AND morphology.morphology_code = specimen.snop_code


To me, this seems to give ok result with the sample data, but I wouldn't be too sure of how it behaves with your real data. 

I'm not completely sure what you are trying to obtain by having that IF condition in WHERE clause. If the objective is to return morphology data and no functions data when specimen.snop_axis='M' and in other cases to return functions data and no morphology data, I'd suggest having the selection logic applied in the columns that are returned, somehow like this:

SELECT
IF(specimen.snop_axis = 'M', morphology_pk, functions_pk) matching_pk,
IF(specimen.snop_axis = 'M', morphology_code, functions_code) matching_code,
IF(specimen.snop_axis = 'M', morphology_nomen, functions_nomen) matching_nomen,
specimen.*,
topography_index.*


instead of just having columns from both tables returned always. But as I said, I'm not quite sure I understand what is required.