I have a situation where a particular statement is not returning all results as it should. There is only one situation I am aware of for use in testing and I have not been able to figure it out.
The purpose of this data is to store materials and categorize them into material types. Say an M3 screw as a material, in the Screws material type. There is a table materialTable_MaterialTypeTable that acts as a many to many between material and material types. We also have a table called supplierMaterialPrices. This has a record per material and per supplier to store the price of that material from that supplier. Now the fun bit. For the purposes of quotes to customers, we have a table called quotedMaterialPrices that stores the quoteID and the quoted price for the material. This is so if they are quoted a special price or the price changes after the fact, the historical price is store.
The problem I am having is in a statement that unions 2 selects, the first select gets all materials that are in the material type selected THAT ARE NOT quoted already, and the second select gets the ONE material that is quoted. This information is used to populate a list of materials.
The statement I have returns all results so far as I am aware except in one circumstance. Material type 83 should ALWAYS return 2 results but only eve returns 1. Material type 77 is a duplicate material type and it always returns 2 as it should. (there are multiple of the same material type so the list allows the customer to choose 2 types of caulk for a project).
As a note, it is possible for a material to have a quoted price but have 0 quantity. If the quote is saved and then edited later and the quantity is reduced to 0, the quoted price remains with a quantity of 0.
Attached is the procedure and some sample data.
ALTER PROCEDURE [dbo].[Select_MaterialTable_By_QuoteID_AND_MaterialTypeID] @QuoteID int, @MaterialTypeID int AS BEGIN SET NOCOUNT ON; DECLARE @QuoteType nvarchar(7) SET @QuoteType = (SELECT QuoteType FROM aQuote WHERE QuoteID=@QuoteID) Select NULL as QuoteID, MT.MaterialID, MT.DisplayPriority, MTT.MaterialTypeID, MTT.MaterialTypeName, MT.MaterialName, SMPT.supplierMaterialPrice AS Price, 0 AS Quantity, null AS Color, MTT.PreferedSupplier AS ChosenSupplier FROM MaterialTable AS MT INNER JOIN materialTable_MaterialTypeTable as mTMTT ON MT.MaterialID = mTMTT.materialID INNER JOIN MaterialTypeTable AS MTT ON mTMTT.MaterialTypeID = MTT.MaterialTypeID FULL OUTER JOIN supplierMaterialPriceTable AS SMPT ON SMPT.supplierID = MTT.PreferedSupplier AND SMPT.materialID = MT.MaterialID WHERE MT.Active = 1 AND MTT.QuoteType = @QuoteType AND MTT.MaterialTypeID = @MaterialTypeID AND MT.MaterialID NOT IN (Select MT.MaterialID FROM MaterialTable AS MT FULL OUTER JOIN quoteMaterialPricesTable AS QMT ON QMT.MaterialID = MT.MaterialID WHERE QMT.QuoteID = @QuoteID) union Select QMT.QuoteID, MT.MaterialID, MT.DisplayPriority, mTMTT.MaterialTypeID, MTT.MaterialTypeName, MT.MaterialName, QMT.Price AS Price, QMT.Quantity AS Quantity, QMT.Color AS Color, QMT.ChosenSupplier as ChosenSupplier FROM MaterialTable AS MT INNER JOIN quoteMaterialPricesTable AS QMT ON QMT.MaterialID = MT.MaterialID AND qmt.MaterialTypeID = @MaterialTypeID INNER JOIN materialTable_MaterialTypeTable AS mTMTT ON MT.MaterialID = mTMTT.materialID INNER JOIN MaterialTypeTable AS MTT ON mTMTT.MaterialTypeID = MTT.MaterialTypeID WHERE QMT.QuoteID = @QuoteID AND mTMTT.MaterialTypeID=@MaterialTypeID order by DisplayPriority END
Google sheet containing sample data:
I am complete baffled by this one and am prepared to be humbled. Please ask any questions you may have.