How To use sub Query in sql server 2005-08
In this article I am going to explain you about sub Query. Sub Query is very useful if you wanted to do any calculation and wanted to hold sub values. We can definitely say taht sub query is one of the very important feature of sql server 2005-08.
In sql server we use multiple types of sub Query which you can find in below example. If you know how to use sub Query in MS SQL then you can solved most of the issues you may face during SQL developemt.
Syntax=>
1.
select * from TbaleName where ColumnName in
(
select ColumnName from tableName
)
2.
select * from
(
select SUM(columnName),SUM(ColumnNameSecond) from TableName
)as AliagTableName
3.
select * from TableName
inner join (select * from tableName)as AliagTableName on AliagTableName.QunColumnName=TableName.QunColumnName
Exapmle =>
CREATE TABLE [dbo].[tbl_CityMaster](
[cID] [int] NULL,
[cName] [nvarchar](100) NULL,
[cCode] [nvarchar](25) NULL,
[sID] [int] NULL,
[CoID] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tbl_CityMaster] ([cID], [cName], [cCode], [sID], [CoID]) VALUES (1, N'Chandigarh', N'160586', 1, 1)
INSERT [dbo].[tbl_CityMaster] ([cID], [cName], [cCode], [sID], [CoID]) VALUES (2, N'Mohali', N'160486', 1, 1)
INSERT [dbo].[tbl_CityMaster] ([cID], [cName], [cCode], [sID], [CoID]) VALUES (3, N'Panchkula', N'160487', 2, 1)
INSERT [dbo].[tbl_CityMaster] ([cID], [cName], [cCode], [sID], [CoID]) VALUES (4, N'Luch', N'160005', 2, 1)
INSERT [dbo].[tbl_CityMaster] ([cID], [cName], [cCode], [sID], [CoID]) VALUES (5, N'Lu', N'160006', 2, 1)
CREATE TABLE [dbo].[tbl_StateMaster](
[SID] [int] NULL,
[SName] [nvarchar](100) NULL,
[SCODE] [nvarchar](25) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tbl_StateMaster] ([SID], [SName], [SCODE]) VALUES (1, N'Punjab', NULL)
INSERT [dbo].[tbl_StateMaster] ([SID], [SName], [SCODE]) VALUES (2, N'Chandigarh', NULL)
INSERT [dbo].[tbl_StateMaster] ([SID], [SName], [SCODE]) VALUES (5, N'', NULL)
1.
select * from tbl_CityMaster
where sID in(select sID from tbl_StateMaster)
2.
select * from
(
select SUM(sid) as AddSum from tbl_StateMaster
)
as TbaleNmae
3.
select * from tbl_CityMaster
inner join (select * from tbl_StateMaster) as Sta on Sta.SID=tbl_CityMaster.sID
If you have any doubts about the query please post the doubds here.