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

问题描述：

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

我有一个工单跟踪网站。在我的new_workorder.php脚本中，我的表单由表cl_billing_codes code>中的值动态生成。提交表单时，值将使用代码ID进入 cl_work_order_billing_codes code>，数量和具有工单ID的列将值绑定到工作单。 p>

我正在尝试构建edit_workorder.php脚本。我需要提取 cl_billing_codes code>的所有值，同时从new_workorder.php表单中加入用户提交的数据量。这是我的最大努力。 p>

  SELECT 
`cl_billing_codes`。``AS`id`，
`cl_billing_codes``billing_code` AS`billing_code`，\  n`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`cinging_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来自`billing_code` 
  code>  pre> 
 
 这样做是行不通的，因为我需要中的所有数据 cl_billing_codes  code>，其中 service  code>列等于1，但只有 cl_work_order_billing_codes  code>中的数量值，其中 work_order_id  code>值 该表中的e等于定义的值，并且两个表中的帐单代码都匹配。 p> 
 
 
 /////附加/////  p> 
 
 

 以下是我要使用的表数据示例。
  http://sqlfiddle.com/#  ！2/49783/1   p> 
  div>

答

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`;

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

相关推荐