SQL Server query that both returns items with a foreign key and items that doesn't have it

Question

I have a discount page where I can add Products that should have a discounted price.

Each product shows up with a number of Product Variants underneath it which can then be unchecked if they shouldn't be part of the discount.

When I press save each "CHECKED" ProductId and VariantId will be stored in a table called StoreDiscountRuleVariant.

If an existing discount page is opened in order to edit then my SQL query is no longer fetching the unchecked product-Variants from the ProductVariant table only the "checked" ones that were saved.

I want the SQL query to both return the ProductVariants that has a matching variantId foreign key in the StoreDiscountRuleVariant table as well as the ones that doesn't. I would also be needing an extra returned column that shows if the variantId was actually present in the StoreDiscountRuleVariant table - for instance isChecked=true/false.

I think this solution would be better than storing both the checked as well as the unchecked variantIds in the StoreDiscountRuleVariant table after saving since new ProductVariants that were added after this discount was made wouldn't show up while editing.

I was pointed in the direction of doing this:

 SELECT p.productid, pv.variantnameSE, pv.sku, pv.variantId, 
 rv.discountrulevariantid, rv.productvariantid FROM 
 dbo.StoreDiscountRuleVariant rv INNER JOIN dbo.Product p ON p.productId = 
 rv.productid LEFT JOIN dbo.productVariant pv ON pv.foreignProductId = 
 p.productId WHERE rv.discountruleid = 24

Here is the result:

Row1:

productid: 1326, variantnamese: Vit, sku: FOD46-1, variant.variantid: 822, discountrulevariantid: 572, discountrulevariant.productvariantid: 1035

ROW2:

productid: 1326, variantnamese: Svart, sku: FOD46-2, variant.variantid: 1035 , discountrulevariantid: 572, discountrulevariant.productvariantid: 1035

Only ROW2/variantid: 1035 exists in StoreDiscountRuleVariant table. So the strange thing for me at least is that discountrulevariantid points to 1035 on both of the rows. shouldn't ROW1's discountrulevariantid: value be empty since variantid:822 doesn't exist in StoreDiscountRuleVariant.

Use LEFT JOIN instead of INNER JOIN https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join — gsharp, Jan 11 '20 at 09:49

score 0 · Answer 1 · answered Jan 11 '20 at 11:52

You need a LEFT JOIN. It is unclear whether you want all products or all storediscountrulevariants.

Whichever you want, that should be the first table. Based on the WHERE clause, I am assuming it the variants:

SELECT rv.productid, rv.productvariantid as variantid, 
       p.productnameSE as productname, pv.variantnameSE as variantname, 
       pv.sku, pv.ishidden, pp.picUrl, pp.picid 
FROM dbo.StoreDiscountRuleVariant rv LEFT JOIN
     dbo.Product p 
     ON p.productId = rv.productid LEFT JOIN
     dbo.productVariant pv
     ON pv.variantId = rv.productvariantid OUTER APPLY 
     (SELECT TOP (1) pp.* 
      FROM productpic pp 
      WHERE pp.productid = p.productid
      ORDER BY pp.isfrontpic DESC
     ) pp 
WHERE rv.discountruleid = 16 
ORDER BY rv.productid, pv.sortOrder;

OUTER APPLY is then correct for the lateral join. CORSS APPLY -- like INNER JOIN -- would remove the unmatched variants.

SQL Server query that both returns items with a foreign key and items that doesn't have it

1 Answers1