CODEWITHSUNDEEP
mysqlsqlcodeigniterjoin
Beep
Beep

Reputation: 2823

query to show both joins

I am trying to build a query to output Comments, Comment_Date, Username or First_Name depending if a UserID or StaffID is pressent in the table row.

I can work it with just UserID or StaffID but when I add the both joins it displays nothing.

So Once again I need to output Comments, Comment_Date, Username and First_Name. Any help is appreciated.

My query

 select('Report_Comments.Comments, Report_Comments.Comment_Date, Login.Username, staff.First_Name')
        ->from('Report_Comments')
        ->join('Login staff', 'Report_Comments.UserID = Login.LoginID')
        ->join('staff', 'Report_Comments.UserID_Staff = staff.StaffID');

Upvotes: 0

Views: 22

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Report_Comments JOIN Login staff ON Report_Comments.UserID =
Login.LoginID, Report_Comments.UserID_Staff

There are missing join keywords in the above. If you wish to join tables, all of them have to be joined with the explicit conditions.

Correct it to:

SELECT `Report_Comments`.`Comments`, `Report_Comments`.`Comment_Date`, `Login`.`Username`, `staff`.`First_Name`
FROM `Report_Comments`
JOIN `Login` ON `Report_Comments`.`UserID` = `Login`.`LoginID
JOIN `staff` ON `Report_Comments`.`UserID_Staff = `staff`.`StaffID` 
WHERE `ReportID` = '53'

Upvotes: 1

axiac
axiac

Reputation: 72226

As you can find out from the documentation (search for "join"), the call to join() has two required arguments. The first one is the joined table, the second one is the join condition. Apparently you squeezed two tables and two join conditions into its arguments and this is why the query generated by CodeIgniter has errors.

Your code should read:

select('Report_Comments.Comments, Report_Comments.Comment_Date, Login.Username,
    staff.First_Name')
->from('Report_Comments')
->join('Login', 'Report_Comments.UserID = Login.LoginID')
->join('staff', 'Report_Comments.UserID_Staff = staff.StaffID')
->where('reportID', '53');

Upvotes: 0

Related Questions