How to show multiple tables list in MySql

How to show multiple table list in mysql I have a query that has two tables one for patients and the second one for patients who has tested for covid-19 I want to show all the patients either they tested or not If tested then show the result with his/her name It only showing the patients who has tested for the covid not every one

how to solve that please ?

here’s my Query

SELECT patient.*  
,covidtest.covidTestResult ,subareas.areaname 
FROM 
( patient LEFT OUTER  JOIN subareas
   ON  patient.town_id = subareas.town_id ) LEFT OUTER join covidtest  ON 
patient.Idnumber = covidtest.Idnumber where 
patient.Idnumber=covidtest.Idnumber and 
covidtest.CovidTestDate=(select max(covidtest.CovidTestDate)from 
covidtest where patient.Idnumber=covidtest.Idnumber) group by covidtest.Idnumber
 UNION SELECT patient.*  ,covidtest.covidTestResult,subareas.areaname  FROM 
 ( patient LEFT OUTER   JOIN subareas   ON 
  patient.town_id = subareas.town_id ) LEFT OUTER join covidtest  ON 
  patient.Idnumber = covidtest.Idnumber  where patient.Idnumber=covidtest.Idnumber 
  and covidtest.CovidTestDate=(select max(covidtest.CovidTestDate) from covidtest where 
  patient.Idnumber=covidtest.Idnumber) group by covidtest.Idnumber;

Answer

here is what you need to do:

SELECT
    patient.*,
    covidtest.covidTestResult,
    subareas.areaname
FROM patient
left join subareas on patient.town_id = subareas.town_id
Left join lateral
    ( select covidTestResult from covidtest 
      where patient.Idnumber = covidtest.Idnumber
      order by CovidTestDate desc 
      limit 1
    ) covidtest on 1=1

Advertisement

Answer