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 2008 Forums
 Transact-SQL (2008)
 Need help with query

Author  Topic 

JMAIL
Starting Member

2 Posts

Posted - 2014-01-21 : 12:20:34
I have following situation:

table_1
pid discount
1 8
2 7
3 9

table_2
pid name price
1 aa 11
1 aa 12
1 aa 13
1 aa 10
2 bb 22
2 bb 33
3 cc 66

I need a query which will generate this result:

pid name price discount
1 aa 11 8
1 aa 12 0
1 aa 13 0
1 aa 10 0
2 bb 22 7
2 bb 33 0
3 cc 66 9

any help?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-01-21 : 13:46:58
Could you explain words the result set?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-21 : 14:05:46
I know I shouldn't guess, but I'm gunna anyway.. :)
DECLARE @table_1 TABLE (pid int, discount int)

INSERT @table_1 VALUES
(1, 8),
(2, 7),
(3, 9)

DECLARE @table_2 TABLE (pid int, name varchar(20), price int)

INSERT @table_2 VALUES
(1, 'aa', 11),
(1, 'aa', 12),
(1, 'aa', 13),
(1, 'aa', 10),
(2, 'bb', 22),
(2, 'bb', 33),
(3, 'cc', 66)


SELECT
pid,
name,
Price,
CASE WHEN RowNum = 1 THEN discount ELSE 0 END AS discount
FROM
(
SELECT
B.pid,
B.name,
B.Price,
A.Discount,
ROW_NUMBER() OVER (PARTITION BY B.pid ORDER BY price DESC) AS RowNum
FROM
@table_1 AS A
INNER JOIN
@table_2 AS B
ON A.pid = B.pid
) AS T
Go to Top of Page

JMAIL
Starting Member

2 Posts

Posted - 2014-01-21 : 18:26:37
Thanks Lamprey, you just save my day.

quote:
Originally posted by Lamprey

I know I shouldn't guess, but I'm gunna anyway.. :)
DECLARE @table_1 TABLE (pid int, discount int)

INSERT @table_1 VALUES
(1, 8),
(2, 7),
(3, 9)

DECLARE @table_2 TABLE (pid int, name varchar(20), price int)

INSERT @table_2 VALUES
(1, 'aa', 11),
(1, 'aa', 12),
(1, 'aa', 13),
(1, 'aa', 10),
(2, 'bb', 22),
(2, 'bb', 33),
(3, 'cc', 66)


SELECT
pid,
name,
Price,
CASE WHEN RowNum = 1 THEN discount ELSE 0 END AS discount
FROM
(
SELECT
B.pid,
B.name,
B.Price,
A.Discount,
ROW_NUMBER() OVER (PARTITION BY B.pid ORDER BY price DESC) AS RowNum
FROM
@table_1 AS A
INNER JOIN
@table_2 AS B
ON A.pid = B.pid
) AS T


Go to Top of Page
   

- Advertisement -