Query - 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

Query

Author

Topic

ruchijani
Starting Member

23 Posts

Posted - 2009-10-27 : 08:03:39

Hiiiii

I have written one query like this

SELECT Id,SUM(CASE WHEN SId = 6 THEN Total END) AS col1,
SUM(CASE WHEN SId = 7 THEN Total END) AS col2'
FROM table1 INNER JOIN table2
ON table1.EID = table2.EID
GROUP BY Id

Output of this query is like below


Id      col1    col2
1	10	24
2	25	30
3       15      10

and i have one table like this


table3
Total1    Total2     Value    MId
1          10        X         2
11         20        Y         2
21         30        Z         2

now I want output like this


Id      col1   col3  col2  col4
1	10	 X     24    Z
2	25	 Z     30    Z
3       15       Y     10    X

col3 = value of table3 where col1 between total1 and total2 and table3.Mid = table2.Mid
col4 = value of table3 where col2 between total1 and total2 and table3.Mid = table2.Mid

Thanks
Ruchi

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-27 : 10:46:28

what is the relationship of table2.Mid with the Id of your first query ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-27 : 13:42:53

[code]SELECT t.Id,
t.col1,
t1.value as col3,
t.col2,
t2.value as col4
from
(
SELECT Id,Mid,SUM(CASE WHEN SId = 6 THEN Total END) AS col1,
SUM(CASE WHEN SId = 7 THEN Total END) AS col2'
FROM table1 INNER JOIN table2
ON table1.EID = table2.EID
GROUP BY Id,Mid
)t
OUTER APPLY (SELECT Value
FROM table3
WHERE Mid=t.Mid
AND t.Col1 BETWEEN Total1 AND Total2)t1
OUTER APPLY (SELECT Value
FROM table3
WHERE Mid=t.Mid
AND t.Col2 BETWEEN Total1 AND Total2)t2
[/code]

ruchijani
Starting Member

23 Posts

Posted - 2009-10-28 : 00:13:55

Hiiii

table1 - Eid,Sid and Mid
table2 - Eid,Id and Total
table3 - Total1,Total2,Value,MId

table1 and table2 related with Eid and table3 and table1 related with Mid

I am using SQL SERVER 2000

Thanks
Ruchi

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-28 : 06:23:12

Reformat of your 1st query.


SELECT 	t2.Id,
	SUM(CASE WHEN t1.SId = 6 THEN t2.Total END) AS col1,
	SUM(CASE WHEN t1.SId = 7 THEN t2.Total END) AS col2'
FROM 	table1 t1
	INNER JOIN table2 t2
	ON 	t1.EID = t2.EID
GROUP BY t2.Id

your 1st query is GROUP BY table2.Id, so for each table2.Id there may be more than 1 table1.Mid, so which Mid do you want to use to join to table3 ?

KH
[spoiler]Time is always against us[/spoiler]

ruchijani
Starting Member

23 Posts

Posted - 2009-11-03 : 03:17:55

Hiii khtan

you can put MId = 2 in where condition of select query

Thanks
Ruchi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources