SQL - Multiple conditions where clause the same column

Question

I have a dataset that looks a little something like this

Subject_ID   Diagnosis_ID  
001          299
001          288
001          233
001          299
002          299
002          233
003          238
004          299
004          233

I'd like to create a new new table consisting of patients that have diagnosis codes 299 and 233.

The code tried so far has been

Select *
  From mytable 
 where diagnosis_id = 299 AND diagnosis_id=233

This hasn't worked -

I've also tried

Select *
  From mytable 
 where diagnosis_id = 299 
INTERSECT
Select *
From mytable 
where diagnosis_id= 233

This hasn't worked either.

Answer 1

select  Subject_ID from (
     Select Distinct Subject_ID, Diagnosis_ID
       From
       Table_1
       Where Diagnosis_ID=299 or Diagnosis_ID=288
)
Group By Subject_ID
Having count(Subject_ID)>=2

Answer 2

Think group by and having:

Select patient_id
From mytable
where diagnosis_id in (299, 233)
group by patient_id
having count(*) = 2;

Note: If your table can have duplicates, then use count(distinct diagnosis_id) = 2.