One of the questions asks what physicians have NOT treated Patient No. 3249. (Hint SELECT ... NOT IN ...)

I currently have a table setup with the physician_id and the patient_no. I can figure out what physicians have worked with Patient 3249 but I have no clue how to figure out what physicians haven't worked with them. The hint says use SELECT ... NOT IN ... but I haven't been able to make it work and haven't found anything online to help.

Any suggestions or pointers?

New contributor
www20 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • I wouldn't use NOT IN for this although you could. I would prefer to use NOT EXISTS. – Sean Lange Nov 7 at 19:55
  • Did you mean to tag both MySQL and SQL Server? – Dale K Nov 7 at 19:57
  • 1
    It would look something like SELECT DISTINCT physician_id FROM yourtable WHERE physician_id NOT IN (SELECT physician_ID FROM yourtable WHERE patient_id = 3249); Essentially: "Select the physicians that have never worked on this one patient". To @SeanLange very good point, this query would be better as a NOT EXISTS where that subquery was correlated on physician_id, but perhaps that's next chapter. – JNevill Nov 7 at 19:57
  • You are right with the sub-select there JNevill! – Ivan Libedinsky Nov 7 at 19:59
  • 1
    What queries have you actually tried? Also, like @DaleK (I like how it looks like Dalek) mentioned, did you mean to tag both SQL Server and MySQL? Are you doing this in both? They're not one in the same. – Tiny Haitian Nov 7 at 20:00

Give X is a query that gives you all IDs of physicians that worked on the patient then

  FROM table
  WHERE physician_id not in (x)

would give you the others.

That is what the hint means.

Of course you can also use a left join here

  FROM Table
  LEFT JOIN (x) AS X ON x.physician_id = Table.physician_id
  WHERE x.physician_id is null

which in some cases is faster than not in, depending on stuff. Both of these queries get the same result.

  • books could be written @WOUNDEDStevenJones (and have been but can be summarized as -- for small sets of data use not in for larger sets use indexes and left joins) – Hogan Nov 7 at 20:48

I would be inclined to use group by and having:

select physician_id
from treatments t
group by phsyician_id
having sum(patient_id = 3249) = 0;

Of course, this gets only physicians who have ever treated any patient but that seems reasonable in the real world.

  • 1
    They should invite you as a guest lecturer – Hogan Nov 7 at 20:52

Your Answer

www20 is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.