Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Return 1 record with data from multiple tables Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 7:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:38 AM
Points: 128, Visits: 486
All,

I have data in numerous tables. The first 2 tables are simple:

tblProducts
-----------------
ProductID
ProductName
CategoryID

tblCategories
----------------
CategoryID
CategoryName

Each product links to a category. That part is easy as I can return the values I need from both tables for the ProductID passed in, like this:

ProductID | ProductName | CategoryID | CategoryName
--------------------------------------------------------------
13 | Magnets | 1 | Misc.

However, I have a third table that will always have 2 records for every Product:

tblProductPricing
----------------
ProductPricingID
ProductID
ProductQuantity (int)
ProductCost

Every product is sold in quantity of 1 and some other quantity (let's say 10). The cost for 1 is $1, the cost for 10 is $8 because there's a discount for buying in bulk.

How can I get the details of the Product plus the 2 records from tblProductPricing in a single record, like this:

ProductID | ProductName | CategoryID | CategoryName | ProductSize1 | ProductCost1 | ProductSize2 | ProductCost2
--------------------------------------------------------------
13 | Magnets | 1 | Misc. | 1 | $1 | 10 | $8

I can't figure it out without seemingly terrible code and I'd like to know if there's an easy way to do this.

Thanks,
Mark



Post #1435220
Posted Monday, March 25, 2013 11:44 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:39 AM
Points: 818, Visits: 2,485
You could do something like the following, if you will always have 2 product prices, with 1 that has a quantity of 1.
select ProductID, ProductName, CategoryID, CategoryName, pp1.ProductQuantity, pp1.ProductCost, ppo.ProductQuantity, ppo.ProductCost
from Product p
inner join Category c on p.CategoryID = c.CategoryID
inner join ProductPricing pp1 on p.ProductID = pp1.productID and pp1.ProductQuantity = 1
inner join ProductPricing ppo on p.ProductID = ppo.productID and ppo.ProductQuantity <> 1

Post #1435269
Posted Tuesday, March 26, 2013 3:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
mickyT (3/25/2013)
You could do something like the following, if you will always have 2 product prices, with 1 that has a quantity of 1.
select ProductID, ProductName, CategoryID, CategoryName, pp1.ProductQuantity, pp1.ProductCost, ppo.ProductQuantity, ppo.ProductCost
from Product p
inner join Category c on p.CategoryID = c.CategoryID
inner join ProductPricing pp1 on p.ProductID = pp1.productID and pp1.ProductQuantity = 1
inner join ProductPricing ppo on p.ProductID = ppo.productID and ppo.ProductQuantity <> 1


If the above solution doesn't get you the expected results....then please post some sample data for further assistance.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1435332
Posted Tuesday, March 26, 2013 7:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:18 AM
Points: 11,982, Visits: 11,003
The solution that MickyT posted looks like it will probably work for the short term. I can tell you from experience that if business is offering quantity discounts currently, they will add more levels in the future. It would be in your best interest to make your solution scalable so that it doesn't matter how many levels of discounts there are.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1435493
Posted Tuesday, March 26, 2013 6:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:38 AM
Points: 128, Visits: 486
micky - I'll check that out.

Sean - what did you have in mind? What can I do to make it more scalable?

Thanks,
Mark



Post #1435720
Posted Tuesday, March 26, 2013 6:44 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
Mark Eckeard (3/26/2013)

Sean - what did you have in mind? What can I do to make it more scalable?


I don't think you can do it totally dynamical, but you may come closer by using crosstab (also known as PIVOT) queries.

Type "crosstab query" in BOL for some examples.

Post #1435724
Posted Tuesday, March 26, 2013 7:31 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:39 AM
Points: 818, Visits: 2,485
Hi
A good article for crosstabs is http://www.sqlservercentral.com/articles/T-SQL/63681/

Here's an example that uses a crosstab allowing for up to 5 different product pricings
-- Set up some test data
;with product as (
SELECT *
FROM (VALUES
(1, 'Beer', 1)
,(2, 'Wine', 1)
,(3, 'Bourbon', 1)
,(4, 'Crisps', 2)
,(5, 'Nuts', 2)
) AS product(ProductId, ProductName,CategoryID)
)
,category as (
SELECT *
FROM (VALUES
(1,'Beverages')
,(2,'Snacks')
) AS product(CategoryID, CategoryName)
)
,productprices as (
SELECT *
FROM (VALUES
(1,1,2.00)
,(1,12,20.00)
,(1,24,36.00)
,(2,1,12.00)
,(2,6,60.00)
,(3,1,45.00)
,(4,10,12.00)
,(5,5,10.00)
,(5,10,16.00)
) AS product(ProductID, ProductQuantity, ProductCost)
)
-- Create query to pivot data
SELECT p.ProductID, p.ProductName, c.CategoryID, c.CategoryName
,MAX(CASE WHEN pp.Seq = 1 THEN pp.ProductQuantity ELSE null END) ProductQuantity1
,MAX(CASE WHEN pp.Seq = 1 THEN pp.ProductCost ELSE null END) ProductCost1
,MAX(CASE WHEN pp.Seq = 2 THEN pp.ProductQuantity ELSE null END) ProductQuantity2
,MAX(CASE WHEN pp.Seq = 2 THEN pp.ProductCost ELSE null END) ProductCost2
,MAX(CASE WHEN pp.Seq = 3 THEN pp.ProductQuantity ELSE null END) ProductQuantity3
,MAX(CASE WHEN pp.Seq = 3 THEN pp.ProductCost ELSE null END) ProductCost3
,MAX(CASE WHEN pp.Seq = 4 THEN pp.ProductQuantity ELSE null END) ProductQuantity4
,MAX(CASE WHEN pp.Seq = 4 THEN pp.ProductCost ELSE null END) ProductCost4
,MAX(CASE WHEN pp.Seq = 5 THEN pp.ProductQuantity ELSE null END) ProductQuantity5
,MAX(CASE WHEN pp.Seq = 5 THEN pp.ProductCost ELSE null END) ProductCost5
FROM Product p
INNER JOIN Category c ON p.CategoryID = c.CategoryID
INNER JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost
FROM ProductPrices
) pp ON p.ProductId = pp.ProductID
GROUP BY p.ProductID, p.ProductName, c.CategoryID, c.CategoryName

Post #1435738
Posted Wednesday, March 27, 2013 7:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:18 AM
Points: 11,982, Visits: 11,003
Mark Eckeard (3/26/2013)
micky - I'll check that out.

Sean - what did you have in mind? What can I do to make it more scalable?

Thanks,
Mark


A dynamic cross tab is what I have in mind. I would like to see some ddl and sample data that is representative of your situation. Micky did a nice job of creating some for you, let me know if that will work.

I do have a question though about what you are doing here. It seems like what you have is quantity break thresholds. In you example you said you have a product that costs $1 at a quantity of 1 and $8 when purchasing 10. Seems to me that you have this off a little bit from how I would build this. I would make the cost at 10 be 80¢. Otherwise you don't know what to charge when the customer purchases 11. Unless the business rules in that case would be charge $8 for 10 and $1 for the remainder.

Let me know about the ddl and sample data. Once I have that we toss this into a dynamic cross tab and it will scale for any number of quantity discounts.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1435901
Posted Thursday, March 28, 2013 3:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
This is how you can transform Mickey's query into a Dynamic Cross tab:

Declare @sql Varchar(MAX) 
-- Set up some test data
Create table product(ProductId Int, ProductName Varchar(30),CategoryID Int)
Create table category(CategoryID Int, CategoryName Varchar(30))
Create table productprices(ProductID Int, ProductQuantity Int, ProductCost Float)
Insert Into product
Select 1, 'Beer', 1
Union ALL
Select 2, 'Wine', 1
Union ALL
Select 3, 'Bourbon', 1
Union ALL
Select 4, 'Crisps', 2
Union ALL
Select 5, 'Nuts', 2
Insert Into category
Select 1,'Beverages'
Union ALL
Select 2,'Snacks'
Insert Into productprices
Select 1,1,2.00
Union ALL
Select 1,12,20.00
Union ALL
Select 1,24,36.00
Union ALL
Select 2,1,12.00
Union ALL
Select 2,6,60.00
Union ALL
Select 3,1,45.00
Union ALL
Select 4,10,12.00
Union ALL
Select 5,5,10.00
Union ALL
Select 5,10,16.00

-- Create query to dynamically pivot data
Select @sql = 'SELECT p.ProductID, p.ProductName, c.CategoryID, c.CategoryName, '
Select @sql = @sql + STUFF((Select DISTINCT ',MAX(CASE WHEN pp.Seq = ' + Cast(pp.Seq As Varchar) + ' THEN pp.ProductQuantity ELSE null END) As ProductQuantity' + Cast(pp.Seq As Varchar)
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost
FROM productprices
) pp FOR XML PATH('')),1,1,'') + ', '
Select @sql = @sql + STUFF((Select DISTINCT ',MAX(CASE WHEN pp.Seq = ' + Cast(pp.Seq As Varchar) + ' THEN pp.ProductCost ELSE null END) As ProductCost' + Cast(pp.Seq As Varchar)
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost
FROM productprices
) pp FOR XML PATH('')),1,1,'')
Select @sql = @sql + ' FROM product p
INNER JOIN category c ON p.CategoryID = c.CategoryID
INNER JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost
FROM productprices
) pp ON p.ProductId = pp.ProductID
GROUP BY p.ProductID, p.ProductName, c.CategoryID, c.CategoryName '
Execute(@sql)



Hope this helps.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1436357
Posted Friday, March 29, 2013 5:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 28, 2014 10:50 AM
Points: 9, Visits: 26
one easy solution, using xml path.




SELECT P.PRODUCTID,P.PRODUCTNAME,C.CategoryID,C.CATEGORYNAME,
STUFF((SELECT ',' + convert(varchar,PP.ProductQuantity)
FROM ProductPricing PP
WHERE PP.PRODUCTID=P.PRODUCTID
FOR XML PATH('')),1,1,'') AS ProductQuantity,
STUFF((SELECT ',' + convert(varchar,PP.ProductCost)
FROM ProductPricing PP
WHERE PP.PRODUCTID=P.PRODUCTID
FOR XML PATH('')),1,1,'') AS ProductCost
FROM PRODUCT P
JOIN Categories C ON C.CategoryID=P.CategoryID



Post #1437151
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse