connecting 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

SQL Server 2005 Forums

Transact-SQL (2005)

connecting two queries

Author

Topic

van73
Starting Member

7 Posts

Posted - 2008-01-08 : 10:04:30

1. There are a lot of tables involved in each query. To make it simpler...
First Query
select CommonCol, aggrecol as Col11 from table1 innerjoin table2 on ..
where <conditions> group by commonCol

Second Query
select commonCol, aggreCol as col12 from (select commonCol, col2 from ...
union
select commonCol, col3 from ..) as table2 group by commonCol

Now I need, CoomonCol, Col11, Col12 in my output

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 10:19:30

Try:-

SELECT t1.commonCol, t1.Col11, t2.Col12 
FROM
(select CommonCol, aggrecol as Col11 from table1 innerjoin table2 on ..
where <conditions> group by commonCol)t1
INNER JOIN
(select commonCol, aggreCol as col12 from (select commonCol, col2 from ... 
union 
select commonCol, col3 from ..) as table2 
group by commonCol
)t2
on t2.commonCol=t1.commonCol

van73
Starting Member

7 Posts

Posted - 2008-01-09 : 02:42:39

I tried the same. The error was showing at t2

A little change worked
SELECT t1.commonCol, t1.Col11, t2.Col12
FROM
(select CommonCol, aggrecol as Col11 from table1 innerjoin table2 on ..
where <conditions> group by commonCol)t1
INNER JOIN
(select commonCol, aggreCol as col12 from (select commonCol, col2 from ...
union
select commonCol, col3 from ..) as table2
group by table2.commonCol
)t2
on t2.commonCol=t1.commonCol

Note that the error was due to missing table2 at group by table2.commonCol

Another question
---------------------
If I have to include a table's columns along with a query which has aggregate columns in its select statement, instead of putting all these tables' columns in the group by clause, which is the best way to do???
For ex:
I have DeptID, avg(sal) grouped by DeptId. Along with this in the select statement I need some detalis to be shown which are related to DeptID from another table say T.
Instead of writing

select T.col1, T.col2, DeptID, avg(sal) from dept innerjoin T on t.Deptid = dept.DeptId group by DeptId, T.col1, T.col2

what is better alternative? Here I have shown only two columns - imagine I have to select more columns from table T and even worse I have to show lot og other table's columns which are also linked to deptId

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-09 : 03:08:24

quote:
Originally posted by van73

I tried the same. The error was showing at t2

A little change worked
SELECT t1.commonCol, t1.Col11, t2.Col12
FROM
(select CommonCol, aggrecol as Col11 from table1 innerjoin table2 on ..
where <conditions> group by commonCol)t1
INNER JOIN
(select commonCol, aggreCol as col12 from (select commonCol, col2 from ...
union
select commonCol, col3 from ..) as table2
group by table2.commonCol
)t2
on t2.commonCol=t1.commonCol

Note that the error was due to missing table2 at group by table2.commonCol

Another question
---------------------
If I have to include a table's columns along with a query which has aggregate columns in its select statement, instead of putting all these tables' columns in the group by clause, which is the best way to do???
For ex:
I have DeptID, avg(sal) grouped by DeptId. Along with this in the select statement I need some detalis to be shown which are related to DeptID from another table say T.
Instead of writing

select T.col1, T.col2, DeptID, avg(sal) from dept innerjoin T on t.Deptid = dept.DeptId group by DeptId, T.col1, T.col2

what is better alternative? Here I have shown only two columns - imagine I have to select more columns from table T and even worse I have to show lot og other table's columns which are also linked to deptId

The results wont be always correct if you take like this:-
select T.col1, T.col2, DeptID, avg(sal) from dept innerjoin T on t.Deptid = dept.DeptId group by DeptId, T.col1, T.col2
as there might be cases where dept data is duplicated(relation not one to one)

so better approach will be to do like this:-

select T.col1, T.col2, T.DeptID, tmp.AvgSal,<other fields> 
from  T
innerjoin 
(SELECT DeptID, avg(sal) AS 'AvgSal'
from dept 
group by DeptID) tmp
on tmp.Deptid = T.DeptId 
inner join <other tables>

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-02-08 : 04:49:03

SELECT DISTINCT E.ISPONSORID,SUMTYPEID1,SUMTYPEID2 FROM EMAILCAMPAIGNDETAILSFORSPONSOR E
INNER JOIN
(SELECT ISPONSORID,SUMTYPEID1=SUM(ITOTALMEMBERS) FROM EMAILCAMPAIGNDETAILSFORSPONSOR
WHERE IEMAILVIEWED = 0
GROUP BY ISPONSORID) M
ON E.ISPONSORID = M.ISPONSORID
INNER JOIN
(SELECT ISPONSORID,SUMTYPEID2=SUM(ITOTALMEMBERS) FROM EMAILCAMPAIGNDETAILSFORSPONSOR
WHERE IEMAILVIEWED IN (1,2)
GROUP BY ISPONSORID) M1
ON M.ISPONSORID = M1.ISPONSORID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources