How to use subquery with inner join in MYSQL?-CodePudding

Do you guys have any idea how to combine this code using subquery. The code works fine but I don't know how to combine it as a one query

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(now(),STR_TO_DATE(dob, '%c/%e/%Y'))), '%Y') 0 AS Age 
FROM tblpatient


SELECT pres.*, 
       CONCAT(p.fname,' ',p.lname) AS pname,
       p.gender,
       p.address 
FROM prescription pres 
INNER JOIN tblpatient p 
WHERE p.id = pres.patient_id 
AND pres.id='$user_id' LIMIT 1"

CodePudding user response：

I believe this is what you were expecting, merging both of your queries together.

SELECT pres.*, 
       CONCAT(p.fname,' ',p.lname) AS pname,
       p.gender,
       p.address,
       DATE_FORMAT(FROM_DAYS(DATEDIFF(now(),STR_TO_DATE(p.dob, '%c/%e/%Y'))), '%Y') 0 AS Age 
FROM prescription pres 
INNER JOIN tblpatient p 
WHERE p.id = pres.patient_id 
AND pres.id='$user_id' LIMIT 1"