what join should use - 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

General SQL Server Forums

New to SQL Server Programming

what join should use

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

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

(
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

the o/p like this

mobid|min_merchant|min_price|max_merchant|max_price|product_name

i need to join another field my_price... table merchant contains price field from that i need to fetch my price where my name matches

thanks in advance

jafrywilson
Constraint Violating Yak Guru

379 Posts

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

Help me please

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-25 : 06:43:04

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=149215

Madhivanan

Failing to plan is Planning to fail

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 06:49:27

sir i need to add another field so only i am asking this question

madhivanan
Premature Yak Congratulator

22864 Posts

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

quote:
Originally posted by jafrywilson

sir i need to add another field so only i am asking this question

You should follow the same topic. You often open new threads for the old thread's discussion

Madhivanan

Failing to plan is Planning to fail

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 06:54:38

quote:
Originally posted by madhivanan

quote:
Originally posted by jafrywilson

sir i need to add another field so only i am asking this question

You should follow the same topic. You often open new threads for the old thread's discussion

Madhivanan

Failing to plan is Planning to fail

ok sir..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-25 : 07:57:53

See this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=149262

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources