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
Perhaps this could be handled by a sub select statement?
Any help appreciated!