joining two select into one - 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 2008 Forums

Transact-SQL (2008)

joining two select into one

Author

Topic

b3bel
Starting Member

6 Posts

Posted - 2010-09-07 : 06:42:48

Hi,
I have two select statements that separately have the right results, but I need them in one table. How is that accomplished?

SELECT s1.SkillName, COUNT(1) as numberOnSite
FROM Skills as s1
INNER JOIN WorkerSkills as ws1 ON s1.SkillID=ws1.SkillID
INNER JOIN Workers as w1 ON ws1.WorkerID=w1.WorkerID
WHERE s1.SkillName='builder'
GROUP BY s1.SkillName

SELECT s2.SkillName, (w2.TeamID) as Team, COUNT(1) as NumberPerTeam
from Skills as s2
inner join WorkerSkills as ws1 on s2.SkillID=ws1.SkillID
inner join Workers as w2 on w2.WorkerID=ws1.WorkerID
WHERE w2.TeamID=11
group by w2.TeamID,s2.SkillName

A perfection of means, and confusion of aims, seems to be our main problem.
Albert Einstein

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-09-07 : 06:45:25

use Union

SELECT s1.SkillName,'' as Team, COUNT(1) as numberOnSite
FROM Skills as s1
INNER JOIN WorkerSkills as ws1 ON s1.SkillID=ws1.SkillID
INNER JOIN Workers as w1 ON ws1.WorkerID=w1.WorkerID
WHERE s1.SkillName='builder'
GROUP BY s1.SkillName
Union
SELECT s2.SkillName, (w2.TeamID) as Team, COUNT(1) as NumberPerTeam
from Skills as s2
inner join WorkerSkills as ws1 on s2.SkillID=ws1.SkillID
inner join Workers as w2 on w2.WorkerID=ws1.WorkerID
WHERE w2.TeamID=11
group by w2.TeamID,s2.SkillName

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Ancy
Starting Member

23 Posts

Posted - 2010-09-07 : 07:30:36

It is advisable to use Union All as Union removes the duplicate rows

SELECT s1.SkillName,'' as Team, COUNT(1) as numberOnSite
FROM Skills as s1
INNER JOIN WorkerSkills as ws1 ON s1.SkillID=ws1.SkillID
INNER JOIN Workers as w1 ON ws1.WorkerID=w1.WorkerID
WHERE s1.SkillName='builder'
GROUP BY s1.SkillName
Union All
SELECT s2.SkillName, (w2.TeamID) as Team, COUNT(1) as NumberPerTeam
from Skills as s2
inner join WorkerSkills as ws1 on s2.SkillID=ws1.SkillID
inner join Workers as w2 on w2.WorkerID=ws1.WorkerID
WHERE w2.TeamID=11
group by w2.TeamID,s2.SkillName

b3bel
Starting Member

6 Posts

Posted - 2010-09-07 : 07:48:02

Thank you both for the replies!

Neither Union nor UNION ALL show the column NumberPerTeam, which is basically what I really need.

Is there a way to write a query in a way that NumberPerTeam would be shown ?

A perfection of means, and confusion of aims, seems to be our main problem.
Albert Einstein

Ancy
Starting Member

23 Posts

Posted - 2010-09-07 : 08:00:13

Since both the queries get the output as a single resultset only the column name specified in the first query will be displayed i.e. numberOnSite
If you need this data as two columns , use the following query

SELECT s1.SkillName,'' as Team, COUNT(1) as numberOnSite, 0 as NumberPerTeam
FROM Skills as s1
INNER JOIN WorkerSkills as ws1 ON s1.SkillID=ws1.SkillID
INNER JOIN Workers as w1 ON ws1.WorkerID=w1.WorkerID
WHERE s1.SkillName='builder'
GROUP BY s1.SkillName, NumberPerTeam
Union All
SELECT s2.SkillName, (w2.TeamID) as Team,0 as numberOnSite, COUNT(1) as NumberPerTeam
from Skills as s2
inner join WorkerSkills as ws1 on s2.SkillID=ws1.SkillID
inner join Workers as w2 on w2.WorkerID=ws1.WorkerID
WHERE w2.TeamID=11
group by w2.TeamID,s2.SkillName, numberOnSite

b3bel
Starting Member

6 Posts

Posted - 2010-09-08 : 02:35:34

quote:
Originally posted by Ancy

Since both the queries get the output as a single resultset only the column name specified in the first query will be displayed i.e. numberOnSite
If you need this data as two columns , use the following query

SELECT s1.SkillName,'' as Team, COUNT(1) as numberOnSite, 0 as NumberPerTeam
FROM Skills as s1
INNER JOIN WorkerSkills as ws1 ON s1.SkillID=ws1.SkillID
INNER JOIN Workers as w1 ON ws1.WorkerID=w1.WorkerID
WHERE s1.SkillName='builder'
GROUP BY s1.SkillName, NumberPerTeam
Union All
SELECT s2.SkillName, (w2.TeamID) as Team,0 as numberOnSite, COUNT(1) as NumberPerTeam
from Skills as s2
inner join WorkerSkills as ws1 on s2.SkillID=ws1.SkillID
inner join Workers as w2 on w2.WorkerID=ws1.WorkerID
WHERE w2.TeamID=11
group by w2.TeamID,s2.SkillName, numberOnSite

If I run this query I get no values, or 0s for number per team; which is a different results set than if I in the query by itself, without the union all.
Is there a way of getting the EXACT same results ?
In addition, is not UNION mostly used for uniting rows and not columns?

thanks

A perfection of means, and confusion of aims, seems to be our main problem.
Albert Einstein

Ancy
Starting Member

23 Posts

Posted - 2010-09-08 : 05:54:16

Hi ,

It would be good if you could provide a sample for the input data and the output that you are expecting.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-08 : 11:41:57

What is your expected output?

b3bel
Starting Member

6 Posts

Posted - 2010-09-12 : 07:08:31

quote:
Originally posted by Lamprey

What is your expected output?

I need a table for a grid as follows
SKILL NAME numberOnSite NumberOnTeam
-------------- ----------------- --------------
sales 12 3

This union did not work for me at all, so I tried to rewrite the SQL using a nested query as follows:

SELECT COUNT(ws2.WorkerID) AS workercount, s1.SkillName,
(select COUNT(ws1.WorkerID) as workercount,s2.SkillName
from WorkerSkills as ws1
inner join Workers on ws1.WorkerID = Workers.WorkerID
INNER Join Skills as s2 on ws1.SkillID=s2.SkillID
where TeamID=11
group by Workers.TeamID, s2.SkillName) as teamcount
FROM Skills as s1
INNER JOIN WorkerSkills as ws2 ON s1.SkillID = ws2.SkillID
INNER JOIN Workers ON ws2.WorkerID = Workers.WorkerID
WHERE s1.SkillID=2-- AND Workers.TeamID=11
group by s1.SkillName

But, I am getting an error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS"

Thanks in advance for your help !

A perfection of means, and confusion of aims, seems to be our main problem.
Albert Einstein

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources