Selecting GROUP BY records condensed from two or more table records

Question

I have two simple tables:

HospitalEpisode:

|ID|DateAdmit|DateDischarge|

and

Unit Episode:

|ID|HospitalEpisodeID|DateAdmit|DateDischarge|

One hospital episode can have multiple Unit episodes.

I need to generate a report of all patients who had two or more unit episodes. This is how I started

SELECT * from HospitalEpisode
INNER JOIN UnitEpisode
ON HospitalEpisode.ID=UnitEpisode.HospitalEpisodeID
GROUP BY HospitalEpisode.ID

This of course does not count unit episodes within a group, does not filter for those where the UnitEpisodes are two or more and does not allow me to step through each unit episode within a hospital episode in order to extract the relevant data. Even if I could get a list of HospitalEpisodes with two or more UnitEpisodes I could then run a second query to pull the specific data for the report. Any assistance appreciated!

Answer 1

USE HAVING clause to get all hospital episodes with 2 or more unit episodes.

SELECT UE.HospitalEpisodeID from HospitalEpisode HE
INNER JOIN UnitEpisode UE
ON HE.ID=UE.HospitalEpisodeID
GROUP BY UE.HospitalEpisodeID
HAVING COUNT(*) >=2

if you want unit episode details matching above criteria, you need to run above select in sub query

SELECT * FROM 
(
    SELECT UE.HospitalEpisodeID from HospitalEpisode HE
    INNER JOIN UnitEpisode UE
    ON HE.ID=UE.HospitalEpisodeID
    GROUP BY UE.HospitalEpisodeID
    HAVING COUNT(*) >=2
)T
JOIN UnitEpisode UE 
on T.HospitalEpisodeID = UE.HospitalEpisodeID