Solved

Join three tables mySQL

Posted on 2011-03-11
2
308 Views
Last Modified: 2012-05-11
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
Comment
Question by:wjstarck
2 Comments
 
LVL 1

Accepted Solution

by:
DrivingInstructor earned 500 total points
ID: 35113940
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
 

Author Closing Comment

by:wjstarck
ID: 35113994
Yes, that does work after all. Thanks.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question