How do I return results for all records in a joined table query when they are missing from one of the joined tables?

Question

I have the following SQL query

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?

Answer 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

Answer 2

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

Answer 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

LEFT JOIN vw_discounts

Answer 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