当连接表查询中的所有记录从一个连接表中丢失时，如何返回它们的结果?

I have the following SQL query

我有以下SQL查询

SELECT tbl_product.prod_id,
       ISNULL (prod_code, '')                       AS prod_code,
       ISNULL (prod_category, '')                   AS prod_category,
       ISNULL(prod_title, '')                       AS prod_title,
       ISNULL(prod_price, 0.0)                      AS prod_price,
       ISNULL(tbl_product.prod_sales_price, 0.0)    AS prod_sales_price,
       ISNULL(prod_desc, '')                        AS prod_desc,
       ISNULL(prod_location, '')                    AS prod_location,
       brand_title,
       pd_discount = CASE
                       WHEN P_Discount > 0 THEN vw_discounts.P_Discount
                       WHEN B_discount > 0 THEN vw_discounts.B_discount
                       WHEN D_Discount > 0 THEN vw_discounts.D_Discount
                       ELSE 0.00
                     END,
       ISNULL((SELECT image_path
               FROM   tbl_image
               WHERE  tbl_image.prod_id = tbl_product.prod_id
                      AND image_primary = 1), '''') AS MainImage,
       ISNULL(prod_pick_from_store, 0)              AS prod_pick_from_store
FROM   tbl_product
       LEFT JOIN tbl_brand
         ON tbl_brand.brand_id = tbl_product.brand_id
       LEFT JOIN tbl_discount
         ON Pd_ProdId = tbl_product.Prod_id
       INNER JOIN vw_discounts
         ON Pd_ProdId = vw_discounts.prod_id
WHERE  tbl_product.prod_id > 270
       AND tbl_product.prod_id < 290

At present this query does not return results for products which do not have an entry in tbl_discount. How can I adapt it so that it does?

目前，这个查询不会返回没有tbl_discount条目的产品的结果。我怎样才能使它适应呢?

4 个解决方案

#1

I think what actually happens is that the records that do not have an entry in vw_discounts are ignored You should use a left join to join with the discounts view too

我认为实际发生的情况是vw_discount中没有条目的记录被忽略，您也应该使用左连接来连接折扣视图

#2

I think you will have to change the Inner Join on vw_discounts to a Left Join

我认为您必须将vw_discount上的内部连接更改为左连接

#3

The following join:

以下连接:

LEFT JOIN tbl_discount

includes all records from left table(s), even if there are no corresponding records in right table.

包含来自左表的所有记录，即使右表中没有相应的记录。

So, seems the problem is not in tbl_discount join, but in vw_discounts join. Try to change it to

因此，问题似乎不在tbl_discount join中，而在vw_discount join中。试着把它改成

LEFT JOIN vw_discounts

#4

You need to change the following joins to outer joins

您需要将以下连接更改为外部连接

LEFT JOIN tbl_brand ON tbl_brand.brand_id = tbl_product.brand_id LEFT JOIN tbl_discount ON Pd_ProdId = tbl_product.Prod_id INNER JOIN vw_discounts ON Pd_ProdId = vw_discounts.prod_id

左加入tbl_brand。brand_id = tbl_product。在Pd_ProdId = tbl_product上加入tbl_discount。Prod_id内连接vw_折扣在Pd_ProdId = vw_discounts.prod_id。

#1