Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


When I run the following script... The error occurred

/*Creating Tables*/
USE [Database1]
GO
CREATE TABLE Tb_Sample1(
[Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Col1] [varchar](10) NULL,
[Col2] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE Tb_Sample2(
[Id] [int] FOREIGN KEY REFERENCES Tb_Sample1(Id) NULL,
[Col1] [varchar](50) NULL
) ON [PRIMARY]
GO

/*Inserting Records*/
Insert Tb_Sample1(Col1,Col2) Values('SQL Server',CURRENT_TIMESTAMP)
Go
Insert Tb_Sample2(Id,Col1) Values(1,'Administration')
Go

/*Using sub-query*/
SELECT a.* FROM Tb_Sample1 a with (Nolock)
WHERE a.Id =
(SELECT b.Id,b.Col1 FROM Tb_Sample2 b with (Nolock))

Msg 116, Level 16, State 1, Line 3
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Reason:
Here, Sub query returns Two columns, These value being compared with one column of the mail query...

Alternate ways:
The query can be changed to any one of the following ways..

SELECT a.* FROM Tb_Sample1 a with (Nolock)
WHERE a.Id in
(SELECT b.Id FROM Tb_Sample2 b with (Nolock))

(or)

Select a.* from Tb_Sample1 a with (Nolock)
where exists
(Select 1 from Tb_Sample2 b with (Nolock) where a.Id = b.Id)

(or)

Select a.* from Tb_Sample1 a with (Nolock) join Tb_Sample2 b with (Nolock) on (a.Id = b.Id)
 
(or)

Select a.* from Tb_Sample1 a with (Nolock), Tb_Sample2 b with (Nolock) where a.Id = b.Id

Recommended way:
Select a.Id, a.Col1, a.Col2 from Tb_Sample1 a with (Nolock) join Tb_Sample2 b with (Nolock) on (a.Id = b.Id)

No comments:

Post a Comment