mysql - 多个左连接和 OR 的 SQL 问题

Question

我正在尝试将上传的 CSV(到 MySQL0)连接到多个其他 SQL 表并创建输出，但遇到一个问题，我从第一个表(上传的 CSV)中看到我的数据，但它与其他表(现有的 MySQL 表)。我检查以确保我 LEFT JOIN 的列的格式相同。问题是我在最终的 JOIN 中使用了 OR 语句，还是可能与此有关陈述是基本的模糊逻辑。

$sql= "SELECT x.*, y.*, z.* FROM tbl_invoice as x LEFT JOIN conversion as y ON 
y.doc_sku = x.doc_sku LEFT JOIN tbl_catalog as z ON z.distributor_sku = COALESCE(y.dist_sku, x.doc_sku) OR x.item_name like '%z.item_description%' WHERE x.timestamp IN (SELECT MAX(timestamp) FROM tbl_invoice)";
    //echo $sql;die;
    $result = $conn->query($sql);
    $res = array();
    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
            $res[] = $row;
        }
    }
    $html = "<table><tr><th>manufacturer</th><th>item_name</th><th>item_quantity</th><th>purchase_price</th><th>purchase_quantity</th><th>dist_sku</th><th>Total</th><th>item_description</th></tr>";
    foreach($res as $key=>$val)
    {
        $html .="<tr><td>".$val['manufacturer']."</td><td>".$val['item_name']."</td><td>".$val['item_quantity']."</td><td>".$val['purchase_price']."</td><td>".$val['purchase_quantity']."</td><td>".$val['dist_sku']."</td><td>".($val['purchase_price']-$val['price'])*$val['purchase_quantity']."</td><td>".$val['item_description']."</td></tr>";
    }
    $html .="</table>";
    echo $html;
    unlink($inputFileName);
    echo "<pre>";print_r($res);die;
} else {
    echo "Sorry, there was an error uploading your file.";
}}?>

Answer 1

您的 join like 语句未按您预期的方式工作。它正在寻找字符串 z.item_description 的文字副本。您的查询应该是

SELECT 
  x.*
  , y.*
  , z.* 
FROM 
  tbl_invoice as x 
  LEFT JOIN conversion as y 
    ON y.doc_sku = x.doc_sku 
  LEFT JOIN tbl_catalog as z 
    ON z.distributor_sku    = COALESCE(y.dist_sku, x.doc_sku) 
    OR x.item_name       like concat('%', z.item_description, '%') 
WHERE 
  x.timestamp IN (
    SELECT 
      MAX(timestamp) 
    FROM 
      tbl_invoice
  )