Join Two Separate Select Statements into One

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)

Join Two Separate Select Statements into One

Author

Topic

pwellar
Starting Member

2 Posts

Posted - 2008-05-15 : 03:25:12

I have two seperate tables that I need to get the data into one select statement. Here is the two separate select statements that I have created.

Select AGEGROUP, [YEAR], SUM(CASES) as Cases FROM tbHosp a
LEFT JOIN tbAGEGROUP b on a.AgeGroupID = b.AgeGroupID
LEFT JOIN tbYEARHOSP f on f.YEARID = a.YEARID
LEFT JOIN tbSEX g on g.SEXID = a.SEXID
where a.AgeGroupID in (1,2,3)
and a.YEARID in (1,2,3)
Group BY AgeGroup, [YEAR]
ORDER BY AgeGroup, [YEAR]

Select AGEGROUP, [YEAR], SUM(POPULATION) as [Population] FROM tbPopulation a
LEFT JOIN tbAGEGROUP b on a.AgeGroupID = b.AgeGroupID
LEFT JOIN tbYEARHOSP f on f.YEARID = a.YEARID
LEFT JOIN tbSEX g on g.SEXID = a.SEXID
where a.AgeGroupID in (1,2,3)
and a.YEARID in (1,2,3)
Group BY AgeGroup, [YEAR]
ORDER BY AgeGroup, [YEAR]

Results - First Select with tbHosp

AgeGroup YEAR CASES
<1 2001 9
<1 2002 32
<1 2003 10
1-4 2001 13
1-4 2002 11
1-4 2003 23
5-9 2001 13
5-9 2002 14
5-9 2003 34

Second Select with tbPopulation
AgeGroup YEAR POPULATION
<1 2001 40686
<1 2002 39768
<1 2003 40438
1-4 2001 174346
1-4 2002 170191
1-4 2003 167223
5-9 2001 247071
5-9 2002 242548
5-9 2003 237816

What I am trying to do is get one result set with Population and Cases together like the following

AgeGroup YEAR CASES POPULATION
<1 2001 9 40686
<1 2002 32 39768
<1 2003 10 40438
1-4 2001 13 174346
1-4 2002 11 170191
1-4 2003 23 167223
5-9 2001 13 247071
5-9 2002 14 242548
5-9 2003 34 237816

Any help would be greatly appreciated.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-15 : 03:51:39

[code]SELECT t1.AgeGroup, t1.[YEAR],t1.Cases,t2.[Population]
FROM
(Select AGEGROUP, [YEAR], SUM(CASES) as Cases FROM tbHosp a
LEFT JOIN tbAGEGROUP b on a.AgeGroupID = b.AgeGroupID
LEFT JOIN tbYEARHOSP f on f.YEARID = a.YEARID
LEFT JOIN tbSEX g on g.SEXID = a.SEXID
where a.AgeGroupID in (1,2,3)
and a.YEARID in (1,2,3)
Group BY AgeGroup, [YEAR]) t1
INNER JOIN
(Select AGEGROUP, [YEAR], SUM(POPULATION) as [Population] FROM tbPopulation a
LEFT JOIN tbAGEGROUP b on a.AgeGroupID = b.AgeGroupID
LEFT JOIN tbYEARHOSP f on f.YEARID = a.YEARID
LEFT JOIN tbSEX g on g.SEXID = a.SEXID
where a.AgeGroupID in (1,2,3)
and a.YEARID in (1,2,3)
Group BY AgeGroup, [YEAR])t2
ON t2.AGEGROUP=t1.AGEGROUP
AND t2.[YEAR]= t1.[YEAR],
ORDER BY t1.AgeGroup, t1.[YEAR][/code]

pwellar
Starting Member

2 Posts

Posted - 2008-05-15 : 10:36:25

Thanks so much

kavithak.official
Starting Member

7 Posts

Posted - 2010-11-11 : 01:55:58

quote:
Originally posted by pwellar

Thanks so much

Hi,
Does the solution work on ms sql 2005?

giving the table name outside the select statement in the query, gives error messages. Syntax not correct. Any help?

thanks and regards,
Kavitha K

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-11 : 02:16:12

quote:
Originally posted by kavithak.official

quote:
Originally posted by pwellar

Thanks so much

Hi,
Does the solution work on ms sql 2005?

giving the table name outside the select statement in the query, gives error messages. Syntax not correct. Any help?

thanks and regards,
Kavitha K

The solution given by visakh works on all version. If you are getting an error, you can post the select statement you are trying.

kavithak.official
Starting Member

7 Posts

Posted - 2010-11-11 : 03:36:39

Thanks.

the query is :
"select Manufacturer, Product, Model, Platform, CategoryName, ProductCategoryName,
NC_Component.SerialNumberAlias as
SERIALNUMBER ,NC_LanAdapter.LANAddress,NC_IPAddress.IPAddress,NC_IPAddress.SubNetMask

from

NC_Product LEFT OUTER JOIN NC_ProductCategory ON
(NC_Product.ProductCategoryOID=NC_ProductCategory.ProductCategoryOID)

LEFT OUTER JOIN

NC_Category ON (NC_ProductCategory.CategoryOID=NC_Category.CategoryOID)

INNER join

NC_Component ON (NC_Component.productalias=nc_product.productoid)

INNER join

NC_Workstation ON (NC_Workstation.workstationoid=nc_component.workstationoid)

LEFT OUTER JOIN

NC_LanAdapter ON (NC_LanAdapter.ComponentOID=NC_Component.ComponentOID)

LEFT OUTER JOIN

( Select IPAddress, SubnetMask from NC_IPAddress INNER JOIN NC_LanAdapter ON
NC_LanAdapter.ComponentOID = NC_IPAddress.LANAdapterOID where
NC_LanAdapter.ComponentOID='0x0245743AA43A77F43B381E65B87D7812') as NC_IPAddress

where NC_LanAdapter.ComponentOID like '0x0245743AA43A77F43B381E65B87D7812'"

And the error msg is :

Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'where'.

thanks and regards,
Kavitha K

kavithak.official
Starting Member

7 Posts

Posted - 2010-11-11 : 03:40:20

quote:
Originally posted by kavithak.official

Thanks.

the query is :
"select Manufacturer, Product, Model, Platform, CategoryName, ProductCategoryName,
NC_Component.SerialNumberAlias as
SERIALNUMBER ,NC_LanAdapter.LANAddress,NC_IPAddress.IPAddress,NC_IPAddress.SubNetMask

from

NC_Product LEFT OUTER JOIN NC_ProductCategory ON
(NC_Product.ProductCategoryOID=NC_ProductCategory.ProductCategoryOID)

LEFT OUTER JOIN

NC_Category ON (NC_ProductCategory.CategoryOID=NC_Category.CategoryOID)

INNER join

NC_Component ON (NC_Component.productalias=nc_product.productoid)

INNER join

NC_Workstation ON (NC_Workstation.workstationoid=nc_component.workstationoid)

LEFT OUTER JOIN

NC_LanAdapter ON (NC_LanAdapter.ComponentOID=NC_Component.ComponentOID)

LEFT OUTER JOIN

( Select IPAddress, SubnetMask from NC_IPAddress INNER JOIN NC_LanAdapter ON
NC_LanAdapter.ComponentOID = NC_IPAddress.LANAdapterOID where
NC_LanAdapter.ComponentOID='0x0245743AA43A77F43B381E65B87D7812')NC_IPAddress

where NC_LanAdapter.ComponentOID like '0x0245743AA43A77F43B381E65B87D7812'"

tried without "as nc_ipaddress". Still getting the same error

And the error msg is :

Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'where'.

thanks and regards,
Kavitha K

thanks and regards,
Kavitha K

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-11 : 04:06:29

select Manufacturer, Product, Model, Platform, CategoryName, ProductCategoryName,
NC_Component.SerialNumberAlias as
SERIALNUMBER ,NC_LanAdapter.LANAddress,NC_IPAddress.IPAddress,NC_IPAddress.SubNetMask

from

NC_Product LEFT OUTER JOIN NC_ProductCategory ON
(NC_Product.ProductCategoryOID=NC_ProductCategory.ProductCategoryOID)

LEFT OUTER JOIN

NC_Category ON (NC_ProductCategory.CategoryOID=NC_Category.CategoryOID)

INNER join

NC_Component ON (NC_Component.productalias=nc_product.productoid)

INNER join

NC_Workstation ON (NC_Workstation.workstationoid=nc_component.workstationoid)

LEFT OUTER JOIN

NC_LanAdapter ON (NC_LanAdapter.ComponentOID=NC_Component.ComponentOID)

LEFT OUTER JOIN

( Select IPAddress, SubnetMask from NC_IPAddress INNER JOIN NC_LanAdapter ON
NC_LanAdapter.ComponentOID = NC_IPAddress.LANAdapterOID where
NC_LanAdapter.ComponentOID='0x0245743AA43A77F43B381E65B87D7812') as NC_IPAddress

--After left outer join you need to have ON
--specify ON followed by condtion

where NC_LanAdapter.ComponentOID like '0x0245743AA43A77F43B381E65B87D7812'"

kavithak.official
Starting Member

7 Posts

Posted - 2010-11-11 : 04:16:04

Hi,

Thank you all. Found the error

. Its my small mistake that i have missed the (on condition) after nc_ipaddress. Inserting the appropriate on condition solves the problem

.

thanks and regards,
Kavitha K

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-11 : 05:04:02

You are welcome

kavithak.official
Starting Member

7 Posts

Posted - 2010-11-24 : 03:53:02

Hi,

I am facing a different problem with the above query. Now this does not work on Oracle database. It is throwing an error even after adding the appropriate "ON Condition" as follows :

"ORA-00905: missing keyword"

Any help?

thanks,
Kavitha K

thanks and regards,
Kavitha K

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-24 : 09:44:56

you may be better off posting this in a Oracle forum then. This is MS SQL Server forum and there are very few experts on Oracle. Please try your luck at www.orafaq.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kavithak.official
Starting Member

7 Posts

Posted - 2010-11-26 : 00:56:20

quote:
Originally posted by visakh16

you may be better off posting this in a Oracle forum then. This is MS SQL Server forum and there are very few experts on Oracle. Please try your luck at www.orafaq.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks Visakh. I will do that.

thanks and regards,
Kavitha K

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources