CODEWITHSUNDEEP
mysql
Kevin Weber
Kevin Weber

Reputation: 198

Multiple MySQL Tables in one query

So, I am trying to query multiple tables.

This is what I have so far, :

SELECT * FROM patientdetails  
WHERE DATEDIFF( CURRENT_DATE, DOB ) /365 <18 
and Gender='Male'  
AND RAMQ in 
(
   SELECT `RAMQ` 
   FROM `pathology` 
   WHERE `Result`='positive'
)

This works just fine, but I need to compare the date to a date field in another table, I have have tried many different solutions.

Solution 1:

SELECT * FROM patientdetails  
WHERE DATEDIFF( pathology.Date, DOB ) /365 <18 
and Gender='Male'  
AND RAMQ in 
(
   SELECT `RAMQ`  
   FROM `pathology` 
   WHERE `Result`='positive'
)

Solution 2:

SELECT * FROM patientdetails p, pathology pp  
WHERE DATEDIFF( pp.Date, p.DOB ) /365 <18 
and p.Gender='Male'  
AND p.RAMQ in 
(
   SELECT `RAMQ` 
   FROM `pathology` 
   WHERE `Result`='positive'
)

None of these seem to work, and its quite frustrating. If someone could provide me with some guidance, it would be greatly appreciated.

Upvotes: 0

Views: 48

Answers (2)

Strawberry
Strawberry

Reputation: 33935

Can we start with this...

SELECT * 
  FROM patientdetails pd 
  JOIN pathology g
    ON g.RAMQ = pd.RAMQ 
   AND g.Result = 'positive'
 WHERE pd.DOB > CURDATE() - INTERVAL 18 YEAR 
   AND pd.Gender='Male';

...?

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

JOIN them this way:

SELECT d.* 
FROM patientdetails  AS d
INNER JOIN pathology AS p ON d.RAMQ = p.RAMQ
WHERE DATEDIFF(p.Date, d.DOB ) /365 < 18 
  AND d.Gender='Male'

Upvotes: 1

Related Questions