Error on subquery - 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

General SQL Server Forums

New to SQL Server Programming

Error on subquery

Author

Topic

SQLFOX
Starting Member

45 Posts

Posted - 2011-03-22 : 09:43:28

I tried to run the query below and got the following error:

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

What am I doing wrong?

Select Firstname, Lastname, Department
From Employees
Where EmployeeID IN
(Select * From Orders
Where UnitPrice>5);

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-22 : 09:59:45

[code]Select Firstname, Lastname, Department
From Employees
Where EmployeeID IN
(Select *EmployeeID From Orders
Where UnitPrice>5);[/code]

That, of course, assumes you have a column named EmployeeId in Orders table.

SQLFOX
Starting Member

45 Posts

Posted - 2011-03-22 : 10:02:32

Worked perfect!

Thank you :-)

SQLFOX
Starting Member

45 Posts

Posted - 2011-03-22 : 10:05:55

I noticed it only worked when I took the * out of the subquery and listed only one column. Can I not list all records in the subquery?

Select Firstname, Lastname, Department
From Employees
Where EmployeeID IN
(Select EmployeeID From Orders
Where UnitPrice>5);

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-22 : 10:14:23

quote:
Originally posted by SQLFOX

I noticed it only worked when I took the * out of the subquery and listed only one column. Can I not list all records in the subquery?

Select Firstname, Lastname, Department
From Employees
Where EmployeeID IN
(Select EmployeeID From Orders
Where UnitPrice>5);

You are comparing only the EmployeeID then why should you include many columns in the subquery?

Madhivanan

Failing to plan is Planning to fail

SQLFOX
Starting Member

45 Posts

Posted - 2011-03-22 : 10:20:06

I would like to see another column in the subquery called 'OrderDate' which is within the Orders table.

The following does not work for me:

Select EmployeeID, Firstname, Lastname, Department
From Employees
Where EmployeeID IN
(Select EmployeeID, OrderDate From Orders
Where UnitPrice>5);

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-22 : 10:28:55

So do you want to match against that column too?

Madhivanan

Failing to plan is Planning to fail

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-22 : 15:03:23

quote:
Originally posted by SQLFOX

Can I not list all records in the subquery?

Simply *No*

Cheers
MIK

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-22 : 15:07:54

Sure you can. Just change your syntax to use an INNER JOIN


Select	e.Firstname, e.Lastname, e.Department, o.OrderDate
From	Employees e
JOIN	Orders o
On	o.EmployeeID = e.EmployeeID
And	o.UnitPrice > 5

[edit typo]

SQLFOX
Starting Member

45 Posts

Posted - 2011-03-24 : 07:29:18

Thanks Guys,

I'm trying to learn more about SQL and I would have used a join but the purpose of this was to learn about subqueries.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources