0

I am trying to "flatten" (don't know the technical term for it) my query, so I will end up with : car_id, category_matrix, factory_equipment, aftermark_equipment

My Query

SELECT c.id as car_id, src.matrix as category_matrix, IF(srce.isFactory, srce.matrix as factory_equipment, srce.matrix as aftermarket_equipment)
FROM residual_cars as c
INNER JOIN residual_categories src on c.category_id = src.id
INNER JOIN residual_category_equipment srce on src.id = srce.category_id
WHERE dbi_code = 'some-dbi-code'

sleasebaq_residual_cars table

id, category_id, dbi_code, etc...

sleasebaq_residual_categories table

id, matrix, etc...

sleasebaq_residual_category_equipment table

id, category_id, is_factory, matrix, etc...

This table has both factory and aftermarket as two separate rows, with isFactory(bool) to differentiate between the two.

Is it the wrong approach to use an IF?

using double joins seems like the wrong approach even though it works.

SELECT c.id as car_id, src.matrix as category_matrix, srcef.matrix as factory_equipment, srce.matrix as aftermark_equipment
    FROM residual_cars as c
    INNER JOIN residual_categories src on c.category_id = src.id
    INNER JOIN residual_category_equipment srcef on src.id = srcef.category_id AND srcef.is_factory = 1
    INNER JOIN residual_category_equipment srce on src.id = srce.category_id AND srcef.is_factory = 0
    WHERE dbi_code = 'some-dbi-code'

Is there a better way of achieving this?

andrelange91
  • 1,018
  • 3
  • 21
  • 48
  • 1
    This transformation is called pivoting. Using joins is one solution. The other one is using conditional aggregation. See answers to the duplicate question for achieving the same result through conditional aggregation. – Shadow Oct 18 '22 at 11:11

0 Answers0