And the syntax in the outer join?

Question

This is a small part of the much larger request that I discovered is the problem.

I am trying to add a filter to my outer join in addition to the primary key. The problem is that I get results with a status code other than 0 or 1, as if it were ignoring the filter.

SELECT * FROM Products P LEFT OUTER JOIN OrderDetails OD ON OD.SkuNum = P.SkuNum LEFT OUTER JOIN OrderHeader OH ON (OD.ShipmentNum = OH.ShipmentNum AND (OH.StatusCode = 0 OR OH.StatusCode = 1)) WHERE P.SkuNum = XXXX 

Please note that if I put this statement (OH.StatusCode = 0 OR OH.StatusCode = 1) in the where clause, it will filter the entire result set by these criteria, which is not what I want.

For this connection, in plain English, I try to say, “Give me all the products and other materials not listed here. If there are any deliveries for this product, give me all the details for them where the shipment has the status 1 or 0”

Is my syntax incorrect or am I missing something? Thanks.

Edit: updated the request to include products to make it clearer what I'm looking for, and fixed a transpose error.

Answer 1

Usually you will always have an OrderHeader for a given OrderDetail, but you may have more than one with a status of 0, 1.

Performing the left join, you get everything for this particular Sku, and then if the order header does not have the status 0, 1, these columns will be NULL .

I would think you want an INNER JOIN . But then this is usually equivalent to placing everything in WHERE , so I'm sure that you fully describe what you want to get here.

After viewing your edit, try the following:

 SELECT * FROM Products P LEFT JOIN ( SELECT OD.SkuNum, OD.ShipmentNum, etc. FROM OrderDetails OD INNER JOIN OrderHeader OH ON OD.ShipmentNum = OH.ShipmentNum AND (OH.StatusCode = 0 OR OH.StatusCode = 1) ) AS Orders ON Orders.SkuNum = P.SkuNum WHERE P.SkuNum = XXXX 

Answer 2

When making a left join, you say that you want to get all the order details, where skunum = xxxx, regardless of whether there are any matches in the order header table.

Based on your request and a simple description in English, it seems to me that you need an internal join. But maybe I do not understand enough or have enough information. The internal connection will only return all order details when the shipment has a status of 1 or 0, and a match with an error.

Answer 3

Here you do not need a view.

By placing the last sentence ON OD.SkuNum = P.SkuNum , LEFT JOIN on Products logically occurs last.

 SELECT * FROM Products P LEFT OUTER JOIN OrderDetails OD INNER JOIN OrderHeader OH ON OD.ShipmentNum = OH.ShipmentNum AND OH.StatusCode IN ( 0, 1 ) ON OD.SkuNum = P.SkuNum WHERE P.SkuNum = 'XXXX' 

Answer 4

Not sure if this is what you are looking for:

 SELECT * FROM OrderHeader OH LEFT OUTER JOIN OrderDetails OD ON OD.ShipmentNum = OH.ShipmentNum AND OD.SkuNum = XXXX WHERE OH.StatusCode IN (0, 1) 

Your expression in plain English seems to mean that you are looking for a specific post. You might want to add a sender to the where clause.

Answer 5

 SELECT * FROM Products P LEFT JOIN ( SELECT OD.* FROM OrderHeader OH INNER JOIN OrderDetails OD ON OH.ShipmentNum = OD.ShipmentNum WHERE OH.StatusCode IN (0,1) ) as orders ON P.SkuNum = orders.SkuNum WHERE P.SkuNum = XXXX 

Answer 6

Try the following:

 SELECT * FROM Products P LEFT OUTER JOIN ( OrderDetails OD INNER JOIN OrderHeader OH ON (OD.ShipmentNum = OH.ShipmentNum AND (OH.StatusCode = 0 OR OH.StatusCode = 1)) ) ON OD.SkuNum = P.SkuNum WHERE P.SkuNum = XXXX