I am using the following query which is returning a huge number of multiplied records.

    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 these tables in the above query (29 x 2000).

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.

[SQLfiddle][1] using simplified query ( 

    SELECT * FROM specimen, topography_index, morphology
    WHERE specimen.topography_index = 'T05'

 ) and showing results specimen table records multiplied by topography_index records table...ie nine results, but there should only be one.

Any help appreciated!


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