On record being excluded from result set

Transact-SQL
,
1

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.

Background:

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:
https://docs.google.com/spreadsheets/d/1PLI8FJ8x7xAYMcs3lzEBX8nRH3YV_UUxLUzixWfRLuk/edit?usp=sharing

I am complete baffled by this one and am prepared to be humbled. Please ask any questions you may have.

Dan

2

Without actually being able to run the query, it is hard to say what might be causing one record to disappear. Couple of things to consider:

When you use UNION, it removes all duplicates. So if your first query had two identical rows, you will get only one row in the final output. You might try changing UNION to UNION ALL to test if that is causing a problem.

When you have a FULL OUTER JOIN and use columns from the tables in the WHERE clause, that effectively turns the join into a LEFT, RIGHT or INNER JOIN.

If you can post consumable data - i.e., create statements to create test tables, and insert statements to insert the data into those tables, along with queries that one can run against the test tables that would make it easier for someone to provide specific answers rather than generalized suggestions.

3

Sorry for delay here. I created a script that creates the required tables and real world data involved that should be able to re-create. I fill follow up with some script data to produce results.

https://drive.google.com/file/d/0B5-u80qKnJVxTFJ0MnFVY21YOHc/view?usp=sharing

`USE [ebrdbms_dev]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[Select_MaterialTable_By_QuoteID_AND_MaterialTypeID]
@QuoteID = 11395,
@MaterialTypeID = 77

SELECT 'Return Value' = @return_value

GO

DECLARE @return_value int

EXEC @return_value = [dbo].[Select_MaterialTable_By_QuoteID_AND_MaterialTypeID]
@QuoteID = 11395,
@MaterialTypeID = 83

SELECT 'Return Value' = @return_value`

This for example, should return duplicate results, but MaterialID 223 is being left out of the second query result set.

`USE [ebrdbms_dev]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[Select_MaterialTable_By_QuoteID_AND_MaterialTypeID]
@QuoteID = 11395,
@MaterialTypeID = 58

SELECT 'Return Value' = @return_value

GO

DECLARE @return_value int

EXEC @return_value = [dbo].[Select_MaterialTable_By_QuoteID_AND_MaterialTypeID]
@QuoteID = 11395,
@MaterialTypeID = 79

SELECT 'Return Value' = @return_value`

This one should also return identical number of rows, but MaterialID 154 (the one returned by the lower select in the union) is being left out of the second result set.

4

I believe I have narrowed it down to being in the AND MT.MaterialID NOT IN () subquery. The materialID IS in the subquery, though in a different category, I just need to also specify category in my clause here.