Entities which have relationship through one table but not another.

[HestonJames]

Hello All,

I'm looking for some help building a query which has currently got my head spinning right around.

I have several entities, these look something like this:

SaleOrder
--------
SaleOrder_ID

SaleOrderItem
-----------
SaleOrderItem_ID
SaleOrder_ID
Manufacturer_ID

Manufacturer
----------
Manufacturer_ID
Name

Acknowledgement
--------------
Acknowledgement_ID
SaleOrder_ID
Manufacturer_ID
Valid (bit)

Now, what I'm looking to query for is a list of sale orders, and manufacturer names for manufacturers that have not acknowledged orders which they are linked too through a sale order item.

So, if a manufacturer is linked to a saleorder through an item, but does not have a valid acknowledgement for that sale order, then I need the manufacturer name, manufacturer_id and saleorder_id returned to me.

This is so I can see a list of which manufacturers have not yet confirmed which orders.

I'd appreciate any help you guys can offer. If you need any more information then please don't hesitate to ask.

Thank you.

Heston

 

[RiverGuy]

Try this:

SELECT m.*, soi.SaleOrder_ID, soi.SaleOrderItem_ID
FROM Manufacturer m
INNER JOIN SaleOrderItem soi 
  ON m.Manufacturer_ID = soi.Manufacturer_ID
LEFT OUTER JOIN Acknowledgement a
  ON m.Manufacturer_ID = a.Manufacturer_ID
  AND soi.SaleOrder_ID = a.SaleOrder_ID
WHERE a.Manufacturer_ID IS NULL

 

[HestonJames]

Oooh great, this looks pretty good.

I've just given it a quick test. There are a couple of changes I need your help making but this is certainly most of the way there.

Couple of things I need:

Select the 'SaleOrder_Number' column from the SaleOrder entity, forgot about this.

Acknowledgements may be marked as valid/invalid dependant on the BIT value of the 'Valid' column, only valid acknowledgements are to be taken into consideration.

An acknowledgement for a particular order/manufacturer is only to be taken into consideration if the 'valid' column is true.

Does that make sense?

Thanks RiverGuy.

Heston

 

[RiverGuy]

This will get you the SaleOrder_Number column, and will also return only those rows meeting either the following two conditions: no acknowledgement on record, or the acknowledgement is not valid.

SELECT m.*, soi.SaleOrder_ID, soi.SaleOrderItem_ID, s.SaleOrder_Number
FROM Manufacturer m
INNER JOIN SaleOrderItem soi
  ON m.Manufacturer_ID = soi.Manufacturer_ID
INNER JOIN SaleOrder s
  ON soi.SaleOrder_ID = s.SaleOrder_ID
LEFT OUTER JOIN Acknowledgement a
  ON m.Manufacturer_ID = a.Manufacturer_ID
  AND soi.SaleOrder_ID = a.SaleOrder_ID
WHERE (a.Manufacturer_ID IS NULL OR [Valid] = 0)

 

[Emtucifor]

I would remove `OR [Valid] = 0`, and put `AND [Valid] = 1` in the LEFT JOIN to Acknowledgement.

It's logically more clear and might perform a little better.

Also, I'd check to see if the following query performs better:

SELECT m.*, soi.SaleOrder_ID, soi.SaleOrderItem_ID, s.SaleOrder_Number
FROM Manufacturer m
INNER JOIN SaleOrderItem soi
  ON m.Manufacturer_ID = soi.Manufacturer_ID
INNER JOIN SaleOrder s
  ON soi.SaleOrder_ID = s.SaleOrder_ID
WHERE
   NOT EXISTS (
      SELECT 1
      FROM Acknowledgement a
      WHERE
         m.Manufacturer_ID = a.Manufacturer_ID
         AND soi.SaleOrder_ID = a.SaleOrder_ID
         AND a.[Valid] = 1
   )