Multi-column subqueries ? - 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 2000 Forums

SQL Server Development (2000)

Multi-column subqueries ?

Author

Topic

nhew
Starting Member

3 Posts

Posted - 2003-11-02 : 19:30:02

Hi All,

Could anyone help me with this problem.

I'm trying to write an sql statement that returns a set of results
based on a subquery that returns two columns.

This is basically what i want.

SELECT * From Employee
Where RevisionNo, EmployeeID IN
(
select max(RevisionNo) as RevisionNo, EmployeeID
from Employee
where (EmployeeID>='00100976') and (EmployeeID<='00101371')
group by EmployeeID
)

Thanks in advance

Nick

nhew
Starting Member

3 Posts

Posted - 2003-11-02 : 19:34:20

Hi All,

Sorry i forgot to add this is the error i received.

Line 2: Incorrect syntax near ','.

I'm very new to sql server and i'm not sure if sql server supports
this type of queries.

Nick

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-02 : 19:52:49

A correlated subquery can reference only one column.

This may work for you.


select e1.* 
from Employee e1
where e1.RevisionNo in (select max(e2.RevisionNo) from Employee e2 where e2.EmployeeID = e1.EmployeeID group by e1.RevisionNo) 
and EmployeeID >= '00100976' 
and EmployeeID <= '00101371'

nhew
Starting Member

3 Posts

Posted - 2003-11-02 : 20:03:38

Wow,

Not only was the reply quick, but i got the result i wanted

Thank you ehorn

Nick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources