MySQl problems, trying to do a left outer join with three tables

debugcn Published at Dev

Sam McLean

I'm trying to pull information from three different tables.

Table one is Monthly Ship Reports- where every report has a key to both the ship, and the officer.

Table two is the Ships Table, which has the ship's name. Table three is the officers table, which has the officer's name.

SELECT ship_monthly_report.*, 
       ships.ship_name, 
       officers.officers_title, 
       officers.first_name, 
       officers.last_name 
WHERE  report_key = '" . $_POST["monthly_report_ID"] . 
"LEFT JOIN Ships 
ON Ship_Monthly_Report.Ship_ID = Ships.Ship_Primary_Key 
LEFT JOIN Officers 
ON Ship_Monthly_Report.Capt_ID = Officers.Officers_PK 
ORDER BY report_line_number ASC

is the query I wrote.. which returns null. This is in a PHP script.

I guess what I'm asking is, can I do this? all the fields are correct. Am I butchering the query or am I trying to do something that is impossible?

Wishan

I think the way you structured the query is incorrect, try this:

SELECT t1.*, t2.Ship_Name, t3.Officers_Title, t3.First_Name, t3.Last_Name
FROM Ship_Monthly_Report t1
LEFT OUTER JOIN Ships t2
ON t1.Ship_ID = t2.Ship_Primary_Key
LEFT OUTER JOIN Officers t3
ON t1.Capt_ID = t3.Officers_PK
WHERE t1.report_key = *[your POST value]*
ORDER BY t1.report_line_number ASC

One of the issues with the query is that the WHERE came before the JOIN. I hope this helped!

Collected from the Internet

Please contact [email protected] to delete if infringement.