SQL Error - Need help with query

[HookEmHorns]

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.

 

[HookEmHorns]

Please disregard this post. I was able to get my solution:

SELECT
pv.*
FROM
PatientVisit pv
LEFT OUTER JOIN PatientVisitResource pvr ON pvr.PatientVisitID = pv.PatientVisitId
WHERE
pvr.PatientVisitID IS NULL

 

[markros]

Yes, it's good and better than NOT IN. For NOT IN to work correctly you needed to specify only one field, e.g.

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

PluralSight Learning Library