i want to display two row with multi column in mysql query-CodePudding

i want results look like this as i attached table this is my query and current results

query
------
SELECT vehicle_brand.brand_name,variant_color.id as variant_color_id,additiona_field_line.additional_id,additiona_field_line.additional_field_value
FROM `vehicle_price_mapper` AS vpm
LEFT JOIN vehicle_fuel_mapper AS vvm ON vvm.id = vpm.vehicle_fuel_mapper_id            
LEFT JOIN vehicle_model ON vehicle_model.id = vvm.v_model_id
LEFT JOIN vehicle_brand ON vehicle_brand.id = vehicle_model.brand_id
LEFT JOIN fuel_master ON fuel_master.id = vvm.fuel_id
LEFT JOIN variant_color ON variant_color.vpm_id = vpm.id
LEFT JOIN additiona_field_line ON additiona_field_line.variant_color_id = variant_color.id

i want two row but when i group by brandname its fetch single but i want to fetch multi addiotional_id and additional_value brandwise. how to fixed into the query

i want multiple additional_id and its value with only two rows of brand,but its fetch only single addiotional_id and additional_value.

i want fetch look like

brand_name  variant_color_id     additional_id       additional_field_value 
---------------------------------------------------------------------------------
BMW           2             {multi Addiotonal id}     {multi Addiotonal value}
Mahindra      13            {multi Addiotonal id}     {multi Addiotonal value}

CodePudding user response：

This way you will get the desired result. Use GROUP_CONCAT, like this:

SELECT vehicle_brand.brand_name,variant_color.id as 
variant_color_id,GROUP_CONCAT(additiona_field_line.additional_id), GROUP_CONCAT(additiona_field_line.additional_field_value)
FROM `vehicle_price_mapper` AS vpm
LEFT JOIN vehicle_fuel_mapper AS vvm ON vvm.id = vpm.vehicle_fuel_mapper_id            
LEFT JOIN vehicle_model ON vehicle_model.id = vvm.v_model_id
LEFT JOIN vehicle_brand ON vehicle_brand.id = vehicle_model.brand_id
LEFT JOIN fuel_master ON fuel_master.id = vvm.fuel_id
LEFT JOIN variant_color ON variant_color.vpm_id = vpm.id
LEFT JOIN additiona_field_line ON additiona_field_line.variant_color_id = variant_color.id
GROUP BY vehicle_brand.brand_name, variant_color.id