php - 将检查添加到大型SQL查询

这是查询：

SELECT tbl_product.id, tbl_productspecification.id AS specificationId,
            tbl_product.ProductId, tbl_seller.CompanyName, tbl_product.ProductName, tbl_product.Description, mst_Categories.id AS 'Category',
            tbl_productspecification.RetailPrice, tbl_productspecification.SalePrice,
            tbl_product.image, tbl_productspecification.Discount, tbl_product.EndTime, tbl_product.Seller_Id
            FROM tbl_product
            LEFT OUTER JOIN tbl_seller ON tbl_seller.SelId = tbl_product.Seller_Id
            LEFT OUTER JOIN mst_Categories ON (mst_Categories.id = tbl_product.Category OR mst_Categories.id = tbl_product.SubCategory)
            LEFT OUTER JOIN tbl_productspecification ON tbl_productspecification.ProductId = tbl_product.ProductId
            LEFT OUTER JOIN mst_image ON mst_image.Product = tbl_product.ProductId
            LEFT OUTER JOIN tbl_dealinterest ON tbl_dealinterest.ProductId = tbl_product.ProductId
            where tbl_product.Active='y' and tbl_product.StartTime <= '".date("Y-m-d H:i:s")."' and tbl_product.EndTime>'".date("Y-m-d")." 06:00:00'
            ".$subquery." ".$groupby;

tbl_dealinterest表具有一些字段：

[BuyerId] [ProductId] [Active]

我需要过滤出tbl_dealinterest中与[BuyerId] [ProductId]匹配且[Active]不等于n的所有记录

我已经尝试了一些方法，但并非所有产品都列在tbl_dealinterest中。仅当有人选择一个选项时，它才会被输入。

最佳答案

这应该过滤掉tbl_dealinterest中的所有匹配记录。假设如果存在记录，则tbl_dealinterest.BuyerId永远不会为NULL。

如果没有匹配项，则tbl_dealinterest表的左连接将为tbl_dealinterest表中的所有字段返回NULL值。 where子句中的“ tbl_dealinterest.BuyerId为NULL”将过滤出匹配项。

SELECT tbl_product.id, tbl_productspecification.id AS specificationId,
            tbl_product.ProductId, tbl_seller.CompanyName, tbl_product.ProductName, tbl_product.Description, mst_Categories.id AS 'Category',
            tbl_productspecification.RetailPrice, tbl_productspecification.SalePrice,
            tbl_product.image, tbl_productspecification.Discount, tbl_product.EndTime, tbl_product.Seller_Id
            FROM tbl_product
            LEFT OUTER JOIN tbl_seller ON tbl_seller.SelId = tbl_product.Seller_Id
            LEFT OUTER JOIN mst_Categories ON (mst_Categories.id = tbl_product.Category OR mst_Categories.id = tbl_product.SubCategory)
            LEFT OUTER JOIN tbl_productspecification ON tbl_productspecification.ProductId = tbl_product.ProductId
            LEFT OUTER JOIN mst_image ON mst_image.Product = tbl_product.ProductId
            LEFT OUTER JOIN tbl_dealinterest ON tbl_dealinterest.BuyerId = tbl_product.BuyerId AND tbl_dealinterest.ProductId = tbl_product.ProductId AND tbl_dealinterest.active <> 'n'
            where tbl_product.Active='y' and tbl_product.StartTime <= '".date("Y-m-d H:i:s")."' and tbl_product.EndTime>'".date("Y-m-d")." 06:00:00'
            AND tbl_dealinterest.BuyerId IS NULL
            ".$subquery." ".$groupby;

关于php - 将检查添加到大型SQL查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13258296/