SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Return 1 record with data from multiple tables


Return 1 record with data from multiple tables

Author
Message
Mark Eckeard
Mark Eckeard
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1141 Visits: 505
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



mickyT
mickyT
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4134 Visits: 3320
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


vinu512
vinu512
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5867 Visits: 1626
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 ;-)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101479 Visits: 18186
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 Modens 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)
Mark Eckeard
Mark Eckeard
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1141 Visits: 505
micky - I'll check that out.

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

Thanks,
Mark



Sergiy
Sergiy
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40211 Visits: 12602
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.
mickyT
mickyT
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4134 Visits: 3320
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


Sean Lange
Sean Lange
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101479 Visits: 18186
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 Modens 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)
vinu512
vinu512
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5867 Visits: 1626
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 ;-)
umarrizwan
umarrizwan
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 49
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search