Join two queries - 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

Join two queries

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-24 : 05:59:39

How to join these queries without union all

select f.mobid, f.merchant, f.price
from (
select mobid, min(price) as minprice
from tbl_merchant group by mobid
) as x inner join tbl_merchant as f on f.mobid = x.mobid and f.price = x.minprice order by mobid

select f.mobid, f.merchant, f.price
from (
select mobid, max(price) as minprice
from tbl_merchant group by mobid
) as x inner join tbl_merchant as f on f.mobid = x.mobid and f.price = x.minprice order by mobid

Very urgent!
Tnx in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-24 : 06:05:09

Do you want the result of the two queries merged?

Madhivanan

Failing to plan is Planning to fail

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-24 : 06:11:47

Ya sure ..I need answer in single table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-24 : 06:17:41

quote:
Originally posted by jafrywilson

Ya sure ..I need answer in single table

Both queries are same. Why do you want duplicate results?

Madhivanan

Failing to plan is Planning to fail

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-24 : 06:24:05

quote:
Originally posted by madhivanan

quote:
Originally posted by jafrywilson

Ya sure ..I need answer in single table

Both queries are same. Why do you want duplicate results?

Madhivanan

Failing to plan is Planning to fail

No that are not same first query returns minimum value and the nxt return maximum value

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-24 : 06:26:12

I need to show both the values min price & max price in 2 columns and mobid,merchant in nxt 2 columns

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-24 : 06:27:58

My o/p must like this Mobid|Merchant|Min_price|Max_price

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-24 : 06:31:59

You dont need two statements. This is enough

select f.mobid, f.merchant, x.minprice, x.maxprice
from (
select mobid, min(price) as minprice, max(price) as maxprice
from tbl_merchant group by mobid
) as x inner join tbl_merchant as f on f.mobid = x.mobid and f.price = x.minprice order by mobid

Madhivanan

Failing to plan is Planning to fail

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-24 : 06:43:22

quote:
Originally posted by madhivanan

You dont need two statements. This is enough

select f.mobid, f.merchant, x.minprice, x.maxprice
from (
select mobid, min(price) as minprice, max(price) as maxprice
from tbl_merchant group by mobid
) as x inner join tbl_merchant as f on f.mobid = x.mobid and f.price = x.minprice order by mobid

Madhivanan

Failing to plan is Planning to fail

It s not working because only one column price is in my table in that column find the min and max value..Ur answer gives this error
Msg 207, Level 16, State 1, Line 1
Invalid column name 'minprice'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'maxprice'.

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-24 : 06:45:22

sorry sir.. It is my mistake...Ur query works ..Thanks a lot..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-24 : 06:47:06