使用 LEFT JOIN 一些对多

Question

clients-----------------------| job_no |  client    |-----------------------| 1      | North_1    || 2      | North_1    || 3      | North_1    || 4      | North_1    || 5      | North_2    || 6      | North_2    || 7      | North_2    || 8      | East       || 9      | East       || 10     | West       |-----------------------orders-----------------------| job_no |  order_no  |-----------------------| 1      | order_1    || 1      | order_2    || 5      | order_4    || 5      | order_5    || 5      | order_6    || 5      | order_7    || 5      | order_8    |-----------------------我有 2 个表（客户和订单），我需要按客户名称（LIKE）检索行？我可以做一些很冗长的事情，比如:-($sql_a = "SELECT job_no, client FROM clients WHERE client LIKE '%North%'";$rows_a = fetchAll($sql_a);foreach($rows_a as $rowsArray_a) {    $sql_b = "SELECT order_no FROM orders WHERE job_no='$rowsArray_a['job_no']";    $rows_b = fetchAll($sql_b);    foreach($rows_b as $rowsArray_b) {        $data[]=array('job'=>$rowsArray_a['job_no'], 'order'=>$rowsArray_b['order_no'], 'client'=>$rowsArray_a['client']    }}达到最终看起来像这样的结果：Job     Order      Client1       order_1    North_11       order_2    North_15       order_4    North_25       order_5    North_25       order_6    North_25       order_7    North_25       order_8    North_2但是有没有更好的方法使用 LEFT JOIN 甚至 LEFT JOIN SELECT，甚至是 SELECT 中的 SELECT？我试过了$sql = SELECT clients.job_no, clients.client, orders.job_no, orders.order_no FROM clients LEFT JOIN orders ON orders.job_no = clients.job_no WHERE clients.client LIKE '%North%'所有订单数据均为NULL并且$sql = "SELECT clients.job_no, clients.client FROM clients LEFT JOIN orders AS o ON o.job_no = (SELECT o.job_no, o.order_no WHERE o.job_no = clients.job_no) WHERE clients.client LIKE '%North%'"但在 WHERE client.client LIKE '%North%' 附近出现语法错误”

Answer 1

SELECT

  c.job_no,

  c.client,

  o.order_no

FROM clients c

LEFT JOIN orders o ON o.job_no = c.job_no

WHERE c.client LIKE '%North%'

您应该根据需要添加clients字段orders。

Answer 2

从上面我已经能够扩展查询以还包括订单值，该值可以为零：这意味着您可以将单独的参数应用于查询中的两个表

SELECT

c.job_no,

c.client,

o.order_no

FROM clients c

LEFT JOIN orders o ON o.job_no = c.job_no

WHERE c.client LIKE '%North%'

AND o.order_value !=null