CODEWITHSUNDEEP
mysqlsql
IlludiumPu36
IlludiumPu36

Reputation: 4304

MySQL - Join tables with IF condition

I have the following query which I want to modify with an IF condition like: 

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

select *
  from specimen
  join topography_index
    on substring(specimen.topography_index, 2, 2) =
       topography_index.topography_index_code
  join morphology
    on morphology.morphology_code = specimen.snop_code
  join functions
    on functions.functions_code = specimen.snop_code
  left join specimen_image_lookup
    on specimen_image_lookup.specimen_fk = specimen.specimen_pk
  left join image
    on image.image_pk = specimen_image_lookup.image_fk
 where specimen.specimen_pk = '$specimen'

How should this query be modified to incorporate this if condition? That is, the IF statement needs to replace:

join morphology
    on morphology.morphology_code = specimen.snop_code
  join functions
    on functions.functions_code = specimen.snop_code

Upvotes: 0

Views: 84

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35583

Include the logic for specimen.snop_axis (equals or, does not equal) into the join conditions of both tables to join like this:

SELECT
      *
FROM specimen
      JOIN topography_index
                  ON SUBSTRING(specimen.topography_index, 2, 2) =
                        topography_index.topography_index_code
      LEFT JOIN morphology
                  ON specimen.snop_axis = 'M' AND morphology.morphology_code = specimen.snop_code
      LEFT JOIN functions
                  ON specimen.snop_axis <>'M' AND functions.functions_code = specimen.snop_code
      LEFT JOIN specimen_image_lookup
                  ON specimen_image_lookup.specimen_fk = specimen.specimen_pk
      LEFT JOIN image
                  ON image.image_pk = specimen_image_lookup.image_fk
WHERE specimen.specimen_pk = '$specimen'
;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269773

A SQL query can only return a fixed set of columns. You can do what you want with dynamic SQL, but not with a regular SQL query.

You can add the extra columns and have them be NULL when the specimen does not match:

The from clause would continue:

left join
image
on image.image_pk = specimen_image_lookup.image_fk left join
morphology
on morphology.morphology_code = specimen.snop_code and specimen.snop_axis = 'M' left join
. . .

I'm not quite sure how your joins continue. But if you include the extra condition, then non-matching specimens will have NULL values for the columns.

Upvotes: 0

Related Questions