please help to add a field - SQL Server Forums

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)

please help to add a field

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 04:14:52

select t1.*,t2.Product_Name from
(
select mobid,
max(case when seq=1 then merchant else null end) as min_merchant,
max(case when seq=1 then price else null end) as min_price,
max(case when bseq=1 then merchant else null end) as max_merchant,
max(case when bseq=1 then price else null end) as max_price
from
(
select row_number() over (partition by mobid order by price) as seq,row_number() over (partition by mobid order by price desc) as bseq,mobid, merchant,price
from tbl_merchant
)t
where (seq=1 or bseq=1) and mobid in(select MOBID from tbl_product where Client_Name='wilson')

group by mobid
) as t1 INNER JOIN tbl_product as t2 on t2.mobid=t1.mobid

In the above query i need to add my_Price field where
my_price is where my name matches for that product

thanks in advance ....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-25 : 05:06:50

From which table does my_price derive?
At least you can learn to prefix your column names, so that we don't have to guess from where the columns derive from.

SELECT		t.MobID,
		MAX(CASE WHEN t.seq = 1 THEN t.Merchant ELSE NULL END) AS min_merchant,
		MAX(CASE WHEN t.seq = 1 THEN t.Price ELSE NULL END) AS min_price,
		MAX(CASE WHEN t.bseq = 1 THEN t.Merchant ELSE NULL END) AS max_merchant,
		MAX(CASE WHEN t.bseq = 1 THEN t.Price ELSE NULL END) AS max_price
FROM		(
			SELECT	ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price DESC) AS bseq,
				ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price) AS seq,
				MobID,
				Merchant,
				Price
			FROM	tbl_Merchant
		) AS t
WHERE		1 IN (t.seq, t.bseq)
		AND t.MobID IN (SELECT p.MobID FROM tbl_Product AS p WHERE p.Client_Name = 'Wilson')
GROUP BY	t.MobID

N 56°04'39.26"
E 12°55'05.63"

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 05:17:22

By mobid i joined two tables, tables product,merchant .. merchant table contains merchant name and price columns
From that i retrieved max_price and min_price ,merchant_names ...
Now i want to show my price in the same table..
My price is nothing but where my name matches

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-25 : 05:19:43

And column "my_price" is stored in tbl_Product table?

N 56°04'39.26"
E 12°55'05.63"

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 05:20:02

for example

wilson 100
jafry 200
marlen 150

min_price merchant wilson
max_price merchant jafry

if marlen is user then my_price would be 150

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 05:23:14

can u able to get my point

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-25 : 05:32:20

quote:
Originally posted by jafrywilson

can u able to get my point

Barely. Remember, all we know about your problem is what you write. We don't have prior knowledge of your application, nor do we have access to it.

Is this what you want?

SELECT		t.MobID,
		z.my_Price AS MyPrice,
		MAX(CASE WHEN t.seq = 1 THEN t.Merchant ELSE NULL END) AS min_merchant,
		MAX(CASE WHEN t.seq = 1 THEN t.Price ELSE NULL END) AS min_price,
		MAX(CASE WHEN t.bseq = 1 THEN t.Merchant ELSE NULL END) AS max_merchant,
		MAX(CASE WHEN t.bseq = 1 THEN t.Price ELSE NULL END) AS max_price
FROM		(
			SELECT	ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price DESC) AS bseq,
				ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price) AS seq,
				MobID,
				Merchant,
				Price
			FROM	tbl_Merchant
		) AS t
INNER JOIN	(
			SELECT	MobID,
				my_Price
			FROM	tbl_Product
			WHERE	Client_Name = 'Wilson'
		) AS z ON z.MobID = t.MobID
WHERE		1 IN (t.seq, t.bseq)
GROUP BY	t.MobID,
		z.my_Price

N 56°04'39.26"
E 12°55'05.63"

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 05:36:52

Sorry...forgive me..

It selects from table_product

My need is to select from table_merchant

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 05:39:40

simple example to my task

table 1

id mobid product description user name
1 aa1 xxxx xxxxxx qqq 111
2 aa1 xxxx xxxxxx www 111
3 aa1 xxxx xxxxxx eee 111
4 aa2 yyyy yyyyyy rrr 222
5 aa2 yyyy yyyyy qqq 222
6 aa3 zzzzz zzzzzz qqq 333

table 2

id mobid merchant price
1 aa1 wilson 10
2 aa1 jafry 11
3 aa1 abcd 8
4 aa2 wilson 5
5 aa2 jafry 4

i need my output like this( my name is wilson)...

name | description | my_price | lowprice_merchant | low_price | maxprice_merchant | max_price

111 xxxxx 10 abcd 8 jafry 11

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 05:42:23

table 1

id mobid product description user name
1 aa1 xxxx xxxxxx qqq 111
2 aa1 xxxx xxxxxx www 111
3 aa1 xxxx xxxxxx eee 111
4 aa2 yyyy yyyyyy rrr 222
5 aa2 yyyy yyyyy qqq 222
6 aa3 zzzzz zzzzzz qqq 333

table 2

id mobid merchant price
1 aa1 wilson 10
2 aa1 jafry 11
3 aa1 abcd 8
4 aa2 wilson 5
5 aa2 jafry 4

i need my output like this( my name is wilson)...

name | description | my_price | lowprice_merchant | low_price | maxprice_merchant | max_price

111 xxxxx 10 abcd 8 jafry 11

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-25 : 05:58:35

See my suggestion dated 08/25/2010 : 05:32:20.
Is that close to what you want?

N 56°04'39.26"
E 12°55'05.63"

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 06:07:09

No.. your query gives mobid,min_price,min_merchant,max_price,max_mrechant

my_price is selected in table_product ..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-25 : 06:08:59

Are you really, REALLY, really sure you ran this piece of code?

SELECT		t.MobID,
		z.my_Price AS MyPrice,
		MAX(CASE WHEN t.seq = 1 THEN t.Merchant ELSE NULL END) AS min_merchant,
		MAX(CASE WHEN t.seq = 1 THEN t.Price ELSE NULL END) AS min_price,
		MAX(CASE WHEN t.bseq = 1 THEN t.Merchant ELSE NULL END) AS max_merchant,
		MAX(CASE WHEN t.bseq = 1 THEN t.Price ELSE NULL END) AS max_price
FROM		(
			SELECT	ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price DESC) AS bseq,
				ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price) AS seq,
				MobID,
				Merchant,
				Price
			FROM	tbl_Merchant
		) AS t
INNER JOIN	(
			SELECT	MobID,
				my_Price
			FROM	tbl_Product
			WHERE	Client_Name = 'Wilson'
		) AS z ON z.MobID = t.MobID
WHERE		1 IN (t.seq, t.bseq)
GROUP BY	t.MobID,
		z.my_Price

N 56°04'39.26"
E 12°55'05.63"

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 06:12:16

did u see how i am selecting min,max price in a single field price... likewise i need to select merchant name from a single field named as merchant in merchant table ... now i want to show that if i am entering as wilson in the page then disply my price along with min,max price

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 06:16:46

ya sure .. i ran this query

mobid my_price min_merchant min_price max_merchant max_price
93667 NULL TEPTRONICS 74.99 Amazon.com Marketplace 89.95
93668 NULL Amazon.com 9.96 TEPTRONICS 44.99

i got this output...

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 06:51:31

it is very confusing please help me

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-25 : 07:01:16

If you want only one merchant name, try

SELECT		t.MobID,
		z.my_Price AS MyPrice,
		t.Merchant ,
                MAX(CASE WHEN t.seq = 1 THEN t.Price ELSE NULL END) AS min_price,
		MAX(CASE WHEN t.bseq = 1 THEN t.Price ELSE NULL END) AS max_price
FROM		(
			SELECT	ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price DESC) AS bseq,
				ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price) AS seq,
				MobID,
				Merchant,
				Price
			FROM	tbl_Merchant
		) AS t
INNER JOIN	(
			SELECT	MobID,
				my_Price
			FROM	tbl_Product
			WHERE	Client_Name = 'Wilson'
		) AS z ON z.MobID = t.MobID
WHERE		1 IN (t.seq, t.bseq)
GROUP BY	t.MobID,
		t.merchant,
                z.my_Price

Madhivanan

Failing to plan is Planning to fail

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 07:04:03

NO This is not my requirement

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 07:05:15

yesterday you send me this code

select t1.*,t2.Product_Name from
(
select mobid,
max(case when seq=1 then merchant else null end) as min_merchant,
max(case when seq=1 then price else null end) as min_price,
max(case when bseq=1 then merchant else null end) as max_merchant,
max(case when bseq=1 then price else null end) as max_price
from
(
select row_number() over (partition by mobid order by price) as seq,row_number() over (partition by mobid order by price desc) as bseq,mobid, merchant,price
from tbl_merchant
)t
where (seq=1 or bseq=1) and mobid

group by mobid
) as t1 INNER JOIN tbl_product as t2 on t2.mobid=t1.mobid

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 07:08:05

(select MOBID from tbl_product where Client_Name='wilson')

i inserted this code for my need ..

if i entered with my name i need to show my price as i described earlier ..

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 07:21:02

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources