Getting a complete result set from input.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Getting a complete result set from input.

Author

Topic

Angate_Dan
Starting Member

9 Posts

Posted - 2014-12-18 : 15:56:39

I am working with some SQL and having a hard time retrieving information that I am looking for. The situation is that there are materials listed in a database with jobs that the materials are used on. There are 2 prices per material based on supplier, and, when a job is saved, the cost of the material at that time is saved with it so that when you later query the job, you get the prices at the time. I am including some pictures of what I am dealing with to try to explain it better.

The diagram:

The stored procedure:

	Select QMT.QuoteID, MT.MaterialID, MTT.MaterialTypeName, MT.MaterialName, MT.RBKPrice as RBKDefault, MT.WimsattPrice as WimsattDefault, QMT.RBKPrice as RBKQuote, QMT.WimsattPrice as WimsattQuote
	  FROM MaterialTable AS MT
		   FULL OUTER JOIN quoteMaterialPricesTable AS QMT
           ON QMT.MaterialID = MT.MaterialID
		   INNER JOIN MaterialTypeTable AS MTT
		   ON MT.MaterialTypeID = MTT.MaterialTypeID	   
	 WHERE (QuoteID = @QuoteID OR QMT.QuoteID IS NULL)
	        AND MTT.QuoteType = @QuoteType

The Output:

Here I can see what the prices were when Quote 1000 was put in, and the prices as they are now. The issue is if I provide a quote id that does not have a price saved for it, I do not get a complete result set, and If I specify a QuoteType (Roofing, Windows, Siding) I get no results at all.

I presume my issue is in my joins but I'm not sure.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-18 : 16:08:18

This:


INNER JOIN MaterialTypeTable AS MTT
		   ON MT.MaterialTypeID = MTT.MaterialTypeID

will eliminate non-matching rows.

Angate_Dan
Starting Member

9 Posts

Posted - 2014-12-18 : 16:16:18

That line is the last part of the joining, so I guess I don't understand.

I guess to be clear, I always want all materials from the given MaterialType regardless if they have been saved for a quote, or this quote. I want the info from quoteMaterialPricesTable is the data has been saved, else Null.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-18 : 16:18:59

Do you want the materials from quoteMaterialPricesTable even if they don't exists in MaterialTypeTable ? Then you'll want to make the inner join a LEFT join.

If that's not it, post some sample data for the tables involved, the output you get with your query and the output you want.

Angate_Dan
Starting Member

9 Posts

Posted - 2014-12-18 : 17:50:16

The items in quoteMaterialPricesTable will always exist in the other tables, that table simply stores the price as it was at the time of the Job.

Example Data:

MaterialTypeTable

1, Shingles, Roofing
2, Cap, Roofing
3, Siding Trim, Siding

MaterialTable
1, 1, Red Shingles, 1.00, 1.00
2, 1, Blue Shingles, 1.00, 1.00
3, 2, Wide Ridge Cap, 1.00, 1.00
4, 2, Narrow Ridge Cap, 1.00, 1.00
5, 3, Sliding Window, 1.00, 1.00
6, 3, Glass Block Window, 1.00, 1.00

quoteMaterialPricesTable
100, 1, 1.25, 1.25
100, 3, 2, 2.25

quote 100 only used Red Shinges and Wide Ridge Cap.

with the query looking for quote 100, and roofing:

100, 1, Shingles, Red Shingles, 1.00, 1.00, 1.25, 1.25
NULL, 2, Shingles, Blue Shingles, 1.00, 1.00, NULL, NULL
100, 3, Cap, Wide Ridge Cap, 1.00, 1.00, 2, 2.25
NULL, 4, Cap, Narrow Ridge Cap, 1.00, 1.00

I want to retrieve ALL items that have a QuoteType of roofing, and if there has been any data stored in quoteMaterialPricesTable for the given QuoteID, return those numbers in the place of the NULLs

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-18 : 19:31:33

Isn't that what you're getting? You have two types of roofing, Shingles and Cap. Each of those has two sub-types, so you should get four rows back, which is what your query gives you. If this is not what you want, please post the output you DO want using the sample data you provided. I just ran your query against your data and that's what I got. It looks correct to me.

Angate_Dan
Starting Member

9 Posts

Posted - 2014-12-18 : 20:06:34

That is what I want, the problem is if I provide a quoteID that is not in the quoteMaterialPricesTable then any items that do show up in that table for another quote id are not returned in my results. So in this case if I provide QuoteID 500 I would not get rows for Red Shingles or Wide Ridge

Angate_Dan
Starting Member

9 Posts

Posted - 2014-12-18 : 20:32:55

The problem I think is with the where statement.

 WHERE (QMT.QuoteID = 100 OR QMT.QuoteID IS NULL)
	        AND MTT.QuoteType = 'Roofing'

The QuoteID here is neither 100 or null, it's 1000 or the id of some other quote.

Angate_Dan
Starting Member

9 Posts

Posted - 2014-12-18 : 21:17:14

Here is the sample data I am working with:

INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (1, N'Cap', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (2, N'Drip', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (3, N'Windows', N'Windows')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (5, N'Starter', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (6, N'WrapAroundLShape', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (7, N'IceShield', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (8, N'Synthetic', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (9, N'RidgeVent', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (10, N'SmartVent', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (11, N'PlumbingFlashing', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (12, N'FlapperVents4Inch', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (13, N'FlapperVents8Inch', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (14, N'Flintlastic', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (15, N'BaseSheet', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (16, N'FlatRoofDrip', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (17, N'LouverVents', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (18, N'CounterFlashings', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (19, N'StepFlashings', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (20, N'RollFlashings', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (21, N'OSBPlywood1_2', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (22, N'OSBPlywood7_16', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (23, N'Gutter', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (24, N'DownSpouts', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (25, N'InOutCorner', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (26, N'LeafRelief', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (27, N'GutterHelmet', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (28, N'LeafTerminator', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (29, N'VentedSofit', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (30, N'SolidSoffit', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (31, N'FaciaCoil', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (32, N'VinylSiding', N'Roofing')
INSERT [dbo].[MaterialTypeTable] ([MaterialTypeID], [MaterialTypeName], [QuoteType]) VALUES (33, N'Skylights', N'Roofing')
SET IDENTITY_INSERT [dbo].[MaterialTypeTable] OFF
SET IDENTITY_INSERT [dbo].[MaterialTable] ON 

INSERT [dbo].[MaterialTable] ([MaterialID], [MaterialTypeID], [MaterialName], [RBKPrice], [WimsattPrice]) VALUES (1, 1, N'Wide L Channel', CAST(10.01 AS Decimal(18, 2)), CAST(11.00 AS Decimal(18, 2)))
INSERT [dbo].[MaterialTable] ([MaterialID], [MaterialTypeID], [MaterialName], [RBKPrice], [WimsattPrice]) VALUES (2, 1, N'Narrow L Channel', CAST(10.00 AS Decimal(18, 2)), CAST(11.00 AS Decimal(18, 2)))
INSERT [dbo].[MaterialTable] ([MaterialID], [MaterialTypeID], [MaterialName], [RBKPrice], [WimsattPrice]) VALUES (3, 2, N'Short Drip', CAST(5.00 AS Decimal(18, 2)), CAST(6.00 AS Decimal(18, 2)))
INSERT [dbo].[MaterialTable] ([MaterialID], [MaterialTypeID], [MaterialName], [RBKPrice], [WimsattPrice]) VALUES (4, 2, N'Long Drip', CAST(5.00 AS Decimal(18, 2)), CAST(6.00 AS Decimal(18, 2)))
INSERT [dbo].[MaterialTable] ([MaterialID], [MaterialTypeID], [MaterialName], [RBKPrice], [WimsattPrice]) VALUES (5, 3, N'Normal Window', CAST(500.00 AS Decimal(18, 2)), CAST(250.00 AS Decimal(18, 2)))
INSERT [dbo].[MaterialTable] ([MaterialID], [MaterialTypeID], [MaterialName], [RBKPrice], [WimsattPrice]) VALUES (6, 1, N'Wide H Channel', CAST(1.25 AS Decimal(18, 2)), CAST(1.50 AS Decimal(18, 2)))
INSERT [dbo].[MaterialTable] ([MaterialID], [MaterialTypeID], [MaterialName], [RBKPrice], [WimsattPrice]) VALUES (9, 1, N'Narrow H Channel', CAST(2.30 AS Decimal(18, 2)), CAST(2.23 AS Decimal(18, 2)))
SET IDENTITY_INSERT [dbo].[MaterialTable] OFF
INSERT [dbo].[quoteMaterialPricesTable] ([QuoteID], [MaterialID], [RBKPrice], [WimsattPrice]) VALUES (100, 1, CAST(9.00 AS Decimal(18, 2)), CAST(10.00 AS Decimal(18, 2)))
INSERT [dbo].[quoteMaterialPricesTable] ([QuoteID], [MaterialID], [RBKPrice], [WimsattPrice]) VALUES (1000, 1, CAST(9.50 AS Decimal(18, 2)), CAST(11.00 AS Decimal(18, 2)))
INSERT [dbo].[quoteMaterialPricesTable] ([QuoteID], [MaterialID], [RBKPrice], [WimsattPrice]) VALUES (1000, 3, CAST(5.50 AS Decimal(18, 2)), CAST(6.00 AS Decimal(18, 2)))
INSERT [dbo].[quoteMaterialPricesTable] ([QuoteID], [MaterialID], [RBKPrice], [WimsattPrice]) VALUES (1000, 4, CAST(2.00 AS Decimal(18, 2)), CAST(54.00 AS Decimal(18, 2)))
INSERT [dbo].[quoteMaterialPricesTable] ([QuoteID], [MaterialID], [RBKPrice], [WimsattPrice]) VALUES (5460, 1, CAST(9.50 AS Decimal(18, 2)), CAST(11.00 AS Decimal(18, 2)))
INSERT [dbo].[quoteMaterialPricesTable] ([QuoteID], [MaterialID], [RBKPrice], [WimsattPrice]) VALUES (5460, 3, CAST(5.54 AS Decimal(18, 2)), CAST(56.00 AS Decimal(18, 2)))

Angate_Dan
Starting Member

9 Posts

Posted - 2014-12-18 : 21:28:46

I think this is a job for a union the more I think about it.

SELECT * FROM MaterialTable...

UNION

Select ...
FROM quoteMaterialPricesTable...

The question is how to I use a distinct select here where I only get a single row, not the normal MaterialTable row separate from the quoted row.

Angate_Dan
Starting Member

9 Posts

Posted - 2014-12-18 : 21:34:16

[code] Select NULL as QuoteID, MT.MaterialID, MTT.MaterialTypeName, MT.MaterialName, MT.RBKPrice as RBK, MT.WimsattPrice as Wimsatt
FROM MaterialTable AS MT
INNER JOIN MaterialTypeTable AS MTT
ON MT.MaterialTypeID = MTT.MaterialTypeID
WHERE MTT.QuoteType = 'Roofing'

UNION

Select QMT.QuoteID, MT.MaterialID, MTT.MaterialTypeName, MT.MaterialName, QMT.RBKPrice as RBK, QMT.WimsattPrice as Wimsatt
FROM MaterialTable AS MT
full outer JOIN quoteMaterialPricesTable AS QMT
ON QMT.MaterialID = MT.MaterialID
INNER JOIN MaterialTypeTable AS MTT
ON MT.MaterialTypeID = MTT.MaterialTypeID
WHERE QMT.QuoteID = 5460[/code]

Here I would like to replace row 1 with 7 and 3 with 8.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-19 : 09:21:02

Using the sample data you last posted, please post the required output from your query

Angate_Dan
Starting Member

9 Posts

Posted - 2014-12-19 : 10:09:43

This seems to get the ideal results.

	 Select NULL as QuoteID, MT.MaterialID, MTT.MaterialTypeName, MT.MaterialName, MT.RBKPrice as RBK, MT.WimsattPrice as Wimsatt
	  FROM MaterialTable AS MT
		   INNER JOIN MaterialTypeTable AS MTT
		   ON MT.MaterialTypeID = MTT.MaterialTypeID	   
	 WHERE MTT.QuoteType = 'Roofing'
		   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 = 1000)

	 UNION 

	 Select QMT.QuoteID, MT.MaterialID, MTT.MaterialTypeName, MT.MaterialName, QMT.RBKPrice as RBK, QMT.WimsattPrice as Wimsatt
	  FROM MaterialTable AS MT
		   full outer JOIN quoteMaterialPricesTable AS QMT
           ON QMT.MaterialID = MT.MaterialID
		   INNER JOIN MaterialTypeTable AS MTT
		   ON MT.MaterialTypeID = MTT.MaterialTypeID	   
	 WHERE QMT.QuoteID = 1000

It puts the desired quoteID in the QuoteID column when there is a corresponding line in quoteMaterialPricesTable, it also shows the price as in the quoteMaterialPriceTable if it is there, and if it is not, shows the default price from the MaterialTable.

My original query had both prices but that wasn't nessisary, I only ever wanted the default price if the quoted price wasn't saved.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-19 : 11:06:30

So, you solved it? Woohoo!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources