SQL Server Query - Weird Behaviour

Question

Consider below SQL.

   SELECT DISTINCT bvc_Order.ID,
            bvc_OrderItem.ProductID,
            bvc_OrderItem_BundleItem.ProductID
FROM dbo.bvc_OrderItem WITH (nolock)
RIGHT OUTER JOIN dbo.bvc_Order WITH (nolock)
LEFT OUTER JOIN dbo.bvc_User WITH (nolock) ON dbo.bvc_Order.UserID = dbo.bvc_User.ID
LEFT OUTER JOIN dbo.Amazon_Merchants WITH (nolock) ON dbo.bvc_Order.CompanyID = dbo.Amazon_Merchants.ID ON dbo.bvc_OrderItem.OrderID = dbo.bvc_Order.ID
LEFT OUTER JOIN dbo.bvc_OrderItem_BundleItem WITH (nolock) ON dbo.bvc_OrderItem.ID = dbo.bvc_OrderItem_BundleItem.OrderItemID
LEFT OUTER JOIN dbo.bvc_Product WITH (nolock) ON dbo.bvc_OrderItem.ProductID = dbo.bvc_Product.ID
WHERE 1=1
AND (bvc_Order.StatusCode <> 1
   AND bvc_Order.StatusCode <> 999)
AND ( bvc_OrderItem.ProductID IN ('28046_00')
   OR bvc_OrderItem_BundleItem.ProductID IN ('28046_00'))
AND bvc_Order.OrderSource = 56;

The query when I execute against my database, it returns 85 rows. Well, that is not correct. If I just remove the part "AND bvc_Order.OrderSource = 56" it returns back 5 rows which is really correct. Strange..... Another thing, if I remove the part

OR bvc_OrderItem_BundleItem.ProductID IN ('28046_00')

it will also return the 5 rows as expected even with bvc_Order.OrderSource filter. I am not sure why it is adding more rows while I am trying to reduce rows by using filters.

the table bvc_OrderItem_BundleItem doesn't contain any rows for the result order ids or OrderItemIDs

[edit]

Thanks guys, I tried to remove the LEFT/RIGHT Join Mix but Query manager doesn't allows only LEFT, it does add at least one RIGHT join. I updated the SQL to remove extra tables and now we have only three. But same result

SELECT DISTINCT dbo.bvc_Order.ID, dbo.bvc_OrderItem.ProductID, dbo.bvc_OrderItem_BundleItem.ProductID AS Expr1
FROM         dbo.bvc_OrderItem 
LEFT OUTER JOIN dbo.bvc_OrderItem_BundleItem ON dbo.bvc_OrderItem.ID = dbo.bvc_OrderItem_BundleItem.OrderItemId 
RIGHT OUTER JOIN dbo.bvc_Order ON dbo.bvc_OrderItem.OrderID = dbo.bvc_Order.ID

WHERE 1=1
AND (bvc_Order.StatusCode <> 1 AND bvc_Order.StatusCode <> 999)
AND ( 
    bvc_OrderItem.ProductID IN ('28046_00')
   OR bvc_OrderItem_BundleItem.ProductID IN ('28046_00')
  )
AND bvc_Order.OrderSource = 56;

[edit]So far, there is no solution for this. I previously pasted a link in my comment with example data outout for both valid/invalid results with queries. here it is again. http://sameers.me/SQLIssue.xlsx

One thing to remember here is that ALL left join is not possible. Let me explain further bvc_Order contains main order record bvc_ORderItem contains Order Items/Products bvc_ORderItem_BundleItem contains child products of the product which are available in bvC_OrderItem table.

Now NOT Every product has child products, so bvc_OrderItem_BundleItem may not have any record (and in current scenario, there is really no valid row for the orders in bvC_OrderItem_BundleItem). In short, in current scenario, there is NO matching row available in bvc_OrderItem_BundleItem table. If I remove that join for now, it is all okay, but in real world, I can't remove that BundleItem table join ofcourse.

thank you

SQL Server Query - Weird Behaviour

6 Answers