Only one expression can be specified in the select

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)

Only one expression can be specified in the select

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2010-02-01 : 21:05:58

I am getting the following error and unsure how to resolve it:

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

My Query:

SELECT * from PatientVisit
WHERE PatientVisitId NOT IN
(SELECT * from PatientVisitResource pvr
JOIN PatientVisit pv on pvr.PatientVisitID = pv.PatientVisitId)

I am essentially trying to write a query that gives me a list of all visits without a Resource. Any insight is appreciated.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-01 : 21:14:29

SELECT PV.* from PatientVisit PV
Left Outer join PatientVisitResource PVR
on PVR.PatientVisitID = PV.PatientVisitId
Where PVR.PatientVisitID is null

JeffS23
Posting Yak Master

212 Posts

Posted - 2010-02-01 : 21:36:04

sodeep -

Thank you very much!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-01 : 21:50:02

you can also use NOT EXISTS


select *
from   PatientVisit PV
where  not exists
       (
           select *
           from   PatientVisitResource PVR
           where  PVR.PatientVisitID = PV.PatientVisitID 
       )

or NOT IN


select *
from   PatientVisit
WHERE  PatientVisitId NOT IN
       (
           select PatientVisitID  
           from  PatientVisitResource
       )

also read this http://weblogs.sqlteam.com/peterl/archive/2007/09/20/Finding-records-in-one-table-not-present-in-another-table.aspx

KH
[spoiler]Time is always against us[/spoiler]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-01 : 23:20:46

Make sure you know the evils of using NOT IN with Null values before using khtan's solution. Not Exists will be much safer.

Harsh Athalye
http://www.letsgeek.net/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources