February 16, 2015 at 5:16 am
Hi
I Have 3 Tables
Please Help me to Get This Result :
ProductId - ProductConfigId (With Min Price) -MinPrice - HasGift
1 > 3 > 70 > 1
2 > 4 > 700 > 1
3 > 7 > 820 > 0
5 > 9 > 55 > 1
------------------------------------------------------
CREATE TABLE [dbo].[TBL_Product](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [varchar](100) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TBL_ProductConfig](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ProductId] [int] NULL,
[Config] [varchar](100) NULL,
[HasGift] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TBL_Price](
[id] [int] IDENTITY(1,1) NOT NULL,
[ProductConfigID] [int] NULL,
[Amount] [int] NULL
) ON [PRIMARY]
GO
Insert into TBL_Product values('P1')
Insert into TBL_Product values('P2')
Insert into TBL_Product values('P3')
Insert into TBL_Product values('P4')
Insert into TBL_Product values('P5')
Insert into TBL_ProductConfig Values (1,'C11',0)
Insert into TBL_ProductConfig Values (1,'C12',1)
Insert into TBL_ProductConfig Values (1,'C13',1)
Insert into TBL_ProductConfig Values (2,'C21',1)
Insert into TBL_ProductConfig Values (3,'C31',1)
Insert into TBL_ProductConfig Values (3,'C32',1)
Insert into TBL_ProductConfig Values (3,'C33',0)
Insert into TBL_ProductConfig Values (3,'C34',0)
Insert into TBL_ProductConfig Values (5,'C51',1)
Insert into TBL_ProductConfig Values (5,'C51',0)
Insert Into Tbl_Price Values(1,100)
Insert Into Tbl_Price Values(2,150)
Insert Into Tbl_Price Values(3,70)
Insert Into Tbl_Price Values(4,700)
Insert Into Tbl_Price Values(5,900)
Insert Into Tbl_Price Values(6,920)
Insert Into Tbl_Price Values(7,820)
Insert Into Tbl_Price Values(8,820)
Insert Into Tbl_Price Values(9,55)
Insert Into Tbl_Price Values(9,75)
February 16, 2015 at 7:20 am
Using row number
SELECT ProductId,ProductConfigId,Amount AS [MinPrice],HasGift
FROM (SELECT p.ID AS [ProductId],c.ID AS [ProductConfigId],a.Amount,c.HasGift,
ROW_NUMBER() OVER (PARTITION BY p.ID ORDER BY a.Amount ASC) AS [RowNum]
FROM dbo.TBL_Product p
JOIN dbo.TBL_ProductConfig c ON c.ProductId = p.ID
JOIN dbo.TBL_Price a ON a.ProductConfigID = c.ID) x
WHERE RowNum = 1
Far away is close at hand in the images of elsewhere.
Anon.
February 16, 2015 at 4:56 pm
sm_iransoftware (2/16/2015)
HiI Have 3 Tables
Please Help me to Get This Result :
ProductId - ProductConfigId (With Min Price) -MinPrice - HasGift
1 > 3 > 70 > 1
2 > 4 > 700 > 1
3 > 7 > 820 > 0
5 > 9 > 55 > 1
------------------------------------------------------
CREATE TABLE [dbo].[TBL_Product](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [varchar](100) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TBL_ProductConfig](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ProductId] [int] NULL,
[Config] [varchar](100) NULL,
[HasGift] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TBL_Price](
[id] [int] IDENTITY(1,1) NOT NULL,
[ProductConfigID] [int] NULL,
[Amount] [int] NULL
) ON [PRIMARY]
GO
Insert into TBL_Product values('P1')
Insert into TBL_Product values('P2')
Insert into TBL_Product values('P3')
Insert into TBL_Product values('P4')
Insert into TBL_Product values('P5')
Insert into TBL_ProductConfig Values (1,'C11',0)
Insert into TBL_ProductConfig Values (1,'C12',1)
Insert into TBL_ProductConfig Values (1,'C13',1)
Insert into TBL_ProductConfig Values (2,'C21',1)
Insert into TBL_ProductConfig Values (3,'C31',1)
Insert into TBL_ProductConfig Values (3,'C32',1)
Insert into TBL_ProductConfig Values (3,'C33',0)
Insert into TBL_ProductConfig Values (3,'C34',0)
Insert into TBL_ProductConfig Values (5,'C51',1)
Insert into TBL_ProductConfig Values (5,'C51',0)
Insert Into Tbl_Price Values(1,100)
Insert Into Tbl_Price Values(2,150)
Insert Into Tbl_Price Values(3,70)
Insert Into Tbl_Price Values(4,700)
Insert Into Tbl_Price Values(5,900)
Insert Into Tbl_Price Values(6,920)
Insert Into Tbl_Price Values(7,820)
Insert Into Tbl_Price Values(8,820)
Insert Into Tbl_Price Values(9,55)
Insert Into Tbl_Price Values(9,75)
You didn't state what your criteria is for selecting one of many product configurations.
Personally, I might choose to use GROUP BY instead of ROW_NUMBER() to get the minimum price.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 16, 2015 at 10:40 pm
How do you use Group By ?
February 16, 2015 at 11:30 pm
sm_iransoftware (2/16/2015)
How do you use Group By ?
If I knew the answer to the following I could show you.
dwain.c (2/16/2015)
You didn't state what your criteria is for selecting one of many product configurations.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 17, 2015 at 12:43 am
We want :
Does the lowest ProductConfig Price, have Gift ?
for example :
Prod1 : (samsung)
- ProdConf1 (samsung-Model1) - Has Gift
- Price 70
- ProdConf2 (samsung-Model2) - Has not Gift
- Price 50
- ProdConf3 (samsung-Model3) - Has Gift
- Price 80
Result : Prod1 Has Not gift (Because ProdConf2 with price 50 , Dose not have Gift)
February 17, 2015 at 1:11 am
Looking again, it appears that David's solution using ROW_NUMBER() is the best approach here. I missed the requirement of retrieving the HasGift column from the configuration table, so using GROUP BY is not really an option.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 17, 2015 at 1:17 am
Thank you all
And
Thank you David Burrows
I didnot Know Row_Number() Befor this.
February 17, 2015 at 1:26 am
sm_iransoftware (2/17/2015)
Thank you allAnd
Thank you David Burrows
I didnot Know Row_Number() Befor this.
Actually ROW_NUMBER was introduced with 2005, you might want to check out the other windowing functions as well, 2014 introduced a few new ones, I'm still on 2008R2 so cannot help you with those.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply