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
x
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;
Advertisement
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