• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

Join three tables mySQL

I want to join three tables: anestheticrecord, patient and anesthmedsgiven

anestheticrecord and patient share the field 'PatNum'
anestheticrecord and anesthmedsgiven share the field 'AnestheticRecordNum'

I've tried the following:

SELECT anestheticrecord.AnestheticRecordNum,anestheticrecord.AnestheticDate,
patient.PatNum as 'Patient #',patient.LName,patient.FName,patient.Gender,patient.Birthdate,patient.Address,patient.Address2,patient.City,patient.Zip,
patient.HmPhone,patient.WirelessPhone,anesthmedsgiven.AnesthMedName,anesthmedsgiven.QtyGiven,anesthmedsgiven.QtyWasted
FROM anestheticrecord,patient,anesthmedsgiven
WHERE ((anestheticrecord.PatNum = patient.PatNum) AND (anesthmedsgiven.AnestheticRecordNum = anestheticrecord.AnestheticRecordNum))
AND DATE_FORMAT(anestheticdate,'%Y-%m-%d') >= '2011-01-01' AND DATE_FORMAT(anestheticdate,'%Y-%m-%d') <= '2011-03-11'

which returns no results. I don't think I can use 2 LEFT JOINS because the three tables don't share a foreign key.

Any suggestions?
0
wjstarck
Asked:
wjstarck
1 Solution
 
DrivingInstructorCommented:
It is quite possible to use 2 left joins for this. The first left join uses the shared key between anestheticrecord and patient while the second left join uses the shared key between anestheticrecord and anesthmedsgiven thus:

 
SELECT * FROM anestheticrecord AS a
LEFT JOIN patient AS p (p.PatNum = a.PatNum)
LEFT JOIN anesthmedsgiven AS m (m.AnestheticRecordNum = a.AnestheticRecordNum)

Open in new window


May not seem real, and may break best practices, I don't know, but I know it can be done as I have done it.
0
 
wjstarckAuthor Commented:
Yes, that does work after all. Thanks.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now