CODEWITHSUNDEEP
mysql
Andurit
Andurit

Reputation: 5762

MySQL - If NOT EXIST return 0

right now I have pretty big query which looks like:

    $sql = "SELECT c.vin, c.case_id, c.claimnumber, c.platenumber, c.axrmrs_id, c.insurer_memberid, c.country, c.date_created, c.totalloss, c.lastcalc_manufacturer_code, c.lastcalc_model_code, c.lastcalc_submodel_code, c.audavin_triggered, c.accident_date, c.registration_date, c.manufacturing_year,
              cl.spareparts, cl.totalcosts, cl.laborhours, cl.laborcosts, cl.calculationdate, cl.paintlabor, cl.paintmaterial, cl.currency, car.manufacturer, car.model, car.submodel, org.name
              GROUP_CONCAT(DISTINCT IF(po.repairmethod LIKE 'L%',po.text,NULL) ORDER BY 1) AS textL,
              GROUP_CONCAT(DISTINCT IF(po.repairmethod LIKE 'E',po.text,NULL) ORDER BY 1) AS textE
            FROM axnmrs_cases AS c
              LEFT JOIN axnmrs_calculations as cl on c.case_id = cl.case_id AND c.country = cl.country
              LEFT JOIN axnmrs_positions as po on c.case_id = po.case_id
              LEFT JOIN car_type as car on car.manufacturer_code = c.lastcalc_manufacturer_code AND car.main_type = c.lastcalc_model_code AND car.subtype_code = c.lastcalc_submodel_code
              LEFT JOIN organization_list as org on org.memberId = c.insurer_memberid
            WHERE c.vin= :vin
              GROUP BY c.vin, c.case_id, c.axrmrs_id";

I have small problem that my last LEFT JOIN QUERY not always return org.name (this is short list so not all IDs are there). However I want to return something like 0 if org.name doesn't EXIST.

Can someone help me how should I do this without breaking LEFT JOIN logic?

Thank you
Kind Regards
Andurit

Upvotes: 1

Views: 2687

Answers (2)

mikeb
mikeb

Reputation: 11267

use IFNULL(org.name, 0) in your select.

Upvotes: 2

Roberto
Roberto

Reputation: 2185

Use coalesce function:

SELECT c.vin, c.case_id, c.claimnumber, c.platenumber, c.axrmrs_id, c.insurer_memberid, c.country, c.date_created, c.totalloss, c.lastcalc_manufacturer_code, c.lastcalc_model_code, c.lastcalc_submodel_code, c.audavin_triggered, c.accident_date, c.registration_date, c.manufacturing_year,
                  cl.spareparts, cl.totalcosts, cl.laborhours, cl.laborcosts, cl.calculationdate, cl.paintlabor, cl.paintmaterial, cl.currency, car.manufacturer, car.model, car.submodel, 

                  COALESCE(org.name, 0) as name -- <--- Check this !!!

                  GROUP_CONCAT(DISTINCT IF(po.repairmethod LIKE 'L%',po.text,NULL) ORDER BY 1) AS textL,
                  GROUP_CONCAT(DISTINCT IF(po.repairmethod LIKE 'E',po.text,NULL) ORDER BY 1) AS textE
                FROM axnmrs_cases AS c
                  LEFT JOIN axnmrs_calculations as cl on c.case_id = cl.case_id AND c.country = cl.country
                  LEFT JOIN axnmrs_positions as po on c.case_id = po.case_id
                  LEFT JOIN car_type as car on car.manufacturer_code = c.lastcalc_manufacturer_code AND car.main_type = c.lastcalc_model_code AND car.subtype_code = c.lastcalc_submodel_code
                  LEFT JOIN organization_list as org on org.memberId = c.insurer_memberid
                WHERE c.vin= :vin
                  GROUP BY c.vin, c.case_id, c.axrmrs_id

you could also use IFNULL instead of COALESCE, in this case they are the same. They return the first parameter that is not null. e.g. coalesce(null,5) = 5, coalesce(3, 5) = 3. The difference between isnull and coalesce is that coalesce can receive more parameters.

Upvotes: 2

Related Questions