(select 1...) error

[semi]

Ok, I'm trying to use a (select 1...) sql subquery and having issues
with it.

This SQL returns a value of 161

select count() from dba.patient p where p.patient_id in
(select patient_id from dba.patient_problem where patient_id =
p.patient_id);

This SQL returns a value of 0

select count() from dba.patient p where p.patient_id in
(select 1 from dba.patient_problem where patient_id = p.patient_id);

I apparently am too ignorant to understand how to utilize a (select
1...) statement. Would someone please point out my error.

Thank you in advance and also if this is the incorrect forum, please
let me know.

[Jim Graham]

Your first query asks for rows in the patient table that have
corresponding rows in the patient_problem table. In a way, it makes
sure the patient_id values match twice. You could use either of the
following queries for the same results.

select count() from dba.patient p

where patient_id IN (select pp.patient_id from dba.patient_problem
pp);

select count() from dba.patient p

where exists (select * from dba.patient_problem pp
where pp.patient_id = p.patient_id);

I prefer to write the query using EXISTS (rather than IN). It's the
same, but easier for me to understand.

Your second query asks for rows in the patent table that have
corresponding rows in patient_problem AND a patient_id of 1.
Rewritten using EXISTS, it is easier to see why it gives an answer of
0.

select count() from dba.patient p

where exists (select * from dba.patient_problem pp
where pp.patient_id = p.patient_id
and p.patient_id = 1);

I hope this helps.