joining two results sets - 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

joining two results sets

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2008-04-08 : 08:22:17

Dear friend, the following is my query.

1.SELECT a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag ='5'

2.SELECT b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag ='6'

i need to join this two resultsets. please give me sample query to join this two result sets

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2008-04-08 : 08:25:53

so that my output will be like this

Avg1 Min1 Avg2 Min2

please

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 08:27:10

A bit of a clue as to what you are trying to achieve would be nice
SELECT a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag ='5'
union all
SELECT b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag ='6'

SELECT a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag in ('5','6')

select *
from
(SELECT a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag ='5') a
cross join
(SELECT b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag ='6') b

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 08:29:13

Assuming there's a single row from each subquery the third option above will give that.

select *
from
(SELECT a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag ='5') a
cross join
(SELECT b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag ='6') b

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2008-04-08 : 08:47:24

Dear nr thanks a lot for the reply

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-08 : 11:20:36

SELECT MAX(CASE WHEN Attribute_Flag ='5' THEN Avg_Binaryvalue ELSE NULL END) AS Avg1, 
MAX(CASE WHEN Attribute_Flag ='5' THEN Min_Binaryvalue ELSE NULL END) AS Min1,
MAX(CASE WHEN Attribute_Flag ='6' THEN Avg_Binaryvalue ELSE NULL END) AS Avg2, 
MAX(CASE WHEN Attribute_Flag ='6' THEN Min_Binaryvalue ELSE NULL END) AS Min2
FROM YourTable
GROUP BY PKCol

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2008-04-09 : 11:11:19

dear nr,

actually i have 816 rows. but if i use your query it gives some 30000 rows.i think becos of cross join. so please give me solution

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2008-04-09 : 12:30:29

Dear friend,

this is my table data

sesnsor_ serial _no Avg_Binaryvalue Min_Binaryvalue Attribute_Flag DateofAccess

1 105 56
5 26-03-2008
2 200 25 6 27-06-2008

3 100 59 5 29-02-2008

4 300 20 6 15-02-2008

like this i will have values for many sensors. so depends on the month selection i ned to disply as like as i have mentioned in my previous thread.
in my front end screen i have month selection option.so depends on the selection of the month i need to get the result set as like metioned in my previous thread. please help me friend

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-09 : 13:05:02

It depends on how you want to join the rows.
Maybe
select *
from
(SELECT sesnsor_serial_no, a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag ='5') a
join (SELECT sesnsor_serial_no, b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag ='6') b
on a.sesnsor_serial_no = b.sesnsor_serial_no

or
select *
from
(SELECT sesnsor_serial_no, a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag ='5') a
full outer join
(SELECT sesnsor_serial_no, b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag ='6') b
on a.sesnsor_serial_no = b.sesnsor_serial_no

Do you already have the averages calculated in the table or do you need to calculate them in the query?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2008-04-09 : 22:01:17

dear nr,

Thanks a lot for the reply. i will try this query and get back to you

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2008-04-10 : 06:55:56

dear friend,

actually i have used this query

SELECT * FROM
(SELECT a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag  ='5' and sensor_serial_no='4' and month(date_time)='3') a,
(SELECT b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag  ='6' and sensor_serial_no='4' and month(date_time)='3') b

it gives 196 rows

actually Attribute_Flag =5 contails 14 rows and Attribute_Flag =6 contails 14 rows.so my total rows sholud be 14 only but if i use this query it gives 196. what could be the problem please help me to solve this please

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources