get min value from multiple tables for each id

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 2005 Forums

Transact-SQL (2005)

get min value from multiple tables for each id

Author

Topic

1wrexham1
Starting Member

2 Posts

Posted - 2010-07-17 : 10:33:27

Hi Experts!

I have 3 tables

tblPrice
PriceID|SupplierID|SkipID|PostcodeID|Cost
1|1|1|1|100
2|1|2|1|200
3|1|3|1|300
4|1|4|1|400
5|1|5|1|500
6|2|1|1|50
7|2|2|1|250
8|2|3|1|250
9|2|4|1|450
10|2|5|1|550

tblSkip
SkipID|Name|Description|Image
1|Mini|aaa|aaa
2|Midi|bbb|bbb
3|Large|ccc|ccc
4|XLarge|ddd|ddd
5|XXLarge|eee|eee

tblMargin
MarginID|SkipID|Margin
1|1|10
2|2|20
3|3|30
4|4|40
5|5|50

I need to get table like this:

SupplierID|SkipID|MinCost|Name|Description|Image|Margin
2|1|50|Mini|aaa|aaa|10
1|2|200|Midi|bbb|bbb|20
2|3|250|Large|ccc|ccc|30
1|4|400|XLarge|ddd|ddd|40
1|5|500|XXLarge|eee|eee|50

I need to get from my first 3 tables cheapest price for each skipID where PostcodeID = @PostcodeID and get rest of the values as it is shown in fourth table...
PLEASE HELP

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-17 : 10:45:18

[code]
DECLARE @tblPrice TABLE
(
PriceID int,
SupplierID int,
SkipID int,
PostcodeID int,
Cost int
)
INSERT INTO @tblPrice
SELECT 1,1,1,1,100 UNION ALL
SELECT 2,1,2,1,200 UNION ALL
SELECT 3,1,3,1,300 UNION ALL
SELECT 4,1,4,1,400 UNION ALL
SELECT 5,1,5,1,500 UNION ALL
SELECT 6,2,1,1,50 UNION ALL
SELECT 7,2,2,1,250 UNION ALL
SELECT 8,2,3,1,250 UNION ALL
SELECT 9,2,4,1,450 UNION ALL
SELECT 10,2,5,1,550

DECLARE @tblSkip TABLE
(
SkipID int,
[Name] varchar(10),
[Description] varchar(10),
[image] varchar(10)
)
INSERT INTO @tblSkip
SELECT 1,'Mini','aaa','aaa' UNION ALL
SELECT 2,'Midi','bbb','bbb' UNION ALL
SELECT 3,'Large','ccc','ccc' UNION ALL
SELECT 4,'XLarge','ddd','ddd' UNION ALL
SELECT 5,'XXLarge','eee','eee'

DECLARE @tblMargin TABLE
(
MarginID int,
SkipID int,
Margin int
)
INSERT INTO @tblMargin
SELECT 1,1,10 UNION ALL
SELECT 2,2,20 UNION ALL
SELECT 3,3,30 UNION ALL
SELECT 4,4,40 UNION ALL
SELECT 5,5,50

SELECT p.SupplierID, p.SkipID, p.Cost, s.[Name], s.[Description], s.[image], m.Margin
FROM (
SELECT *,
row_no = row_number() OVER (PARTITION BY p.SkipID ORDER BY Cost)
FROM @tblPrice p
) p
INNER JOIN @tblSkip s ON p.SkipID = s.SkipID
INNER JOIN @tblMargin m ON p.SkipID = m.SkipID
WHERE p.row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

1wrexham1
Starting Member

2 Posts

Posted - 2010-07-17 : 10:59:08

Worked perfect!! Thanks a lot...
Because I have lots of different PostcodeID in database, where should I insert WHERE PostcodeID = @PostcodeID filter??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-17 : 11:40:24

[code]
WHERE p.row_no = 1
AND p.PostcodeID = @PostcodeID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Sachin.Nand

2937 Posts

Posted - 2010-07-17 : 11:42:23

Insert it here


SELECT  *,
                        row_no = row_number() OVER (PARTITION BY p.SkipID ORDER BY Cost)
                FROM    @tblPrice p where  PostcodeID = @PostcodeID

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources