Show all patients with insurance and without insurance

Question

I have been trying to do this for over 2 hours but simply cannot figure it out.

I have 2 tables - 'patient' with 'PatientNum' column, and 'insurance_cover' with 'insuranceCode', 'patientNum' columns.

I want to show all patients with insurance, with their patient number and the amount of different insurance companies they are covered by(this is the part I've been having trouble with).

This is what I want the output to look like as the explaination may be confusing

  Insurance Cover | Patient Number | Number of insurances 
    -------------------------------------------------------
    With Insurance| 1              | 3
    With Insurance| 2              | 1
    With Insurance| 3              | 1
    No Insurance  | 4              | N/A
    No Insurance  | 5              | N/A 

Also I realise I need to use UNION, but I haven't been able to get the first part working yet so haven't attempted that yet

Here is my current attempt

SELECT  CONCAT('With Insurance ', pat.`PatientNum`) 
                          AS `Insurance cover + Patient Number`,
        CONCAT(pat.`PatientFirstname`, ' ', pat.`PatientSurname`)
                          AS `Patient Name`,
        COUNT(`patientNum`) GROUP BY `patientNum`   
  FROM  `patient` AS pat,
        `insurance_cover` AS ins
 WHERE ins.`PatientNum` = pat.`PatientNum`
   AND ins.PatientNum IN (SELECT ins.`PatientNum` 
                            FROM `insurance_cover`)
 GROUP BY pat.`PatientNum`;

Any help is appreciated

Table definitions as requested are at http://imgur.com/a/7k22r (I cannot insert pictures with low rep)

Answer 1

You should use a query like:

SELECT patientNum,
       number_of_insurances,
       (CASE number_of_insurances WHEN 0 THEN 'Not covered' ELSE 'Covered' END)
  FROM (
      SELECT patient.patientNum, 
             count(*) as number_of_insurances, 
        FROM patient
   LEFT JOIN insurance_cover ON patient.patientNum = insurance_cover.patientNum
   GROUP BY patient.patientNum
      ) AS S

Edit: According to comments below, you cannot use JOIN. So here is another (less efficient) answer:

SELECT (CASE (SELECT COUNT(*)
                FROM insurance_cover AS i1
               WHERE i1.patientNum = p.patientNum
             )
        WHEN 0 THEN 'Not covered'
        ELSE 'Covered'
        END) AS covered,
       p.patientNum,
       (SELECT COUNT(*)
          FROM insurance_cover AS i2
         WHERE i2.patientNum = p.patientNum
       ) AS number_of_insurances
  FROM patient p