I am using the following query which is returning a huge number of multiplied records - i.e. a cartesian product:

    SELECT * FROM specimen, topography_index, morphology, functions 
    WHERE 
    SUBSTRING(specimen.topography_index, 2, 2) = topography_index.topography_index_code 
    AND 
    if(specimen.snop_axis = 'M', morphology.morphology_code = specimen.snop_code, functions.functions_code = specimen.snop_code) 
    AND 
    specimen.topography_index = '_ORGAN_'
    ORDER BY 
    (specimen.room = 'f') DESC,specimen.snop_code

The number of records being returned is around 59,000.  However, specimen table only has 469 records.  The morphology and functions tables have around 2000 records each, and the topography_index table has 29 records, so I'm guessing that the results are being multiplied by the number of records in 'topography_index' and 'morphology' or 'functions' tables (29 x 2000), whichever one that doesn't have a join in the if clause.

How should the query be written so that this doesn't happen?  

Note that GROUP BY is NOT an option as the query is so slow with all the records being returned.

[http://sqlfiddle.com/#!2/2bda8/1][1]

Any help appreciated!


  [1]: http://sqlfiddle.com/#!2/2bda8/1