Show patient_id, first_name, last_name, and attending physician's specialty-CodePudding

How do I use SQL to show patient_id, first_name, last_name, and attending physician's specialty?

Show patient_id, first_name, last_name, and attending physician's specialty.

Show only the patients who has a diagnosis as 'Epilepsy' and the physician's first name is 'Lisa'

Check patients, admissions, and physicians tables for required information.

SELECT a.first_name ,a.last_name,a.patient_id FROM patients a
     JOIN admissions AS n ON a.patient_id= n.patient_id
    WHERE n.diagnosis='Epilepsy'
UNION 
   SELECT specialty
     FROM admissions AS n
     JOIN physicians AS p ON n.attending_physician_id=p.physician_id
    WHERE p.first_name='Lisa'
ORDER BY RAND()
LIMIT 6;

patients table

patient_id  INT
first_name  TEXT
last_name   TEXT
gender  CHAR(1)
birth_date  DATE
city    TEXT
primary key icon    province_id CHAR(2)
allergies   TEXT
height  INT
weight  INT

admission table

patient_id  INT
admission_date  DATE
discharge_date  DATE
diagnosis   TEXT
primary key icon    attending_physician_id  INT

physicins table

physician_id    INT
first_name  TEXT
last_name   TEXT
specialty   TEXT

CodePudding user response：

Here's a solution. You should also look at how data appear (mixed case) or even long descriptions (such as Epilepsy ABC).

select i.patient_id, i.first_name, i.last_name, 
 coalesce(p.specialty,'not provided') as physician_specialty --coalesce in case you decide to left join at some point
from patients i
join admission a
  on i.patient_id = a.patient_id 
join physicians p 
  on a.attending_physician_id = p.physician_id
where upper(a.diagnosis) like '%EPILEPSY%'
  and upper(p.first_name) = 'LISA'