选择表B的所有表A左连接列，其中多个轮胎不会影响显示表A的全部

Question

I have a work order tracking site. On my new_workorder.php script my form is dynamically generated by values in table cl_billing_codes. When the form is submitted, the values go into cl_work_order_billing_codes with the code id, and the quantity and a column with the work order id to tie the values to the work order.

I am in the process of trying to build an edit_workorder.php script. I need to pull all the values of cl_billing_codes while left joining the quantities of the user submitted data from the new_workorder.php form. Here is my best effort.

SELECT
  `cl_billing_codes`.`id`                          AS `id`,
  `cl_billing_codes`.`billing_code`                AS `billing_code`,
  `cl_billing_codes`.`desc`                        AS `desc`,
  `cl_billing_codes`.`point_value`                 AS `point_value`,
  `cl_work_orders_billing_codes`.`quantity`        AS `quantity`,
  `cl_work_orders_billing_codes`.`billing_code_id` AS `id2`
FROM
  `cl_billing_codes`
  LEFT JOIN `cl_work_orders_billing_codes`
      ON `billing_code` = `cl_work_orders_billing_codes`.`billing_code_id`
WHERE
  `cl_billing_codes`.`service` = '1'
  AND `cl_work_orders_billing_codes`.`work_order_id` = 1585
ORDER BY `billing_code`

Doing it this way doesn't work because I need all the data in cl_billing_codes where the service column equals 1, but only the quantity values in the cl_work_order_billing_codes where the work_order_id value in that table equals a defined value and the billing codes match in both tables.

///// Additional /////

Here is a sample of my table data to work with. http://sqlfiddle.com/#!2/49783/1

Answer 1

I think you just need to move the condition on the work_orders_billing_codes table into the on clause. Then if there is no match, you will still get all the appropraite records in billing_codes.

SELECT bc.`id` AS `id`, bc.`billing_code` AS `billing_code`, bc.`desc` AS `desc`,
       bc.`point_value` AS `point_value`,
       wobc.`quantity` AS `quantity`,
       wobc.`billing_code_id` AS `id2` 
FROM `cl_billing_codes` bc LEFT JOIN
     `cl_work_orders_billing_codes` wobc
     ON bc.id = wobc.`billing_code_id` AND wobc.`work_order_id` = 1585
WHERE bc.`service` =  '1' 
ORDER BY `billing_code`;