Beep Beep - 7 months ago 10
SQL Question

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');

Answer

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');