Codeigniter join multiple tables with where clauses

Question

I have four tables that I need to join together, with one of those tables having two different records I need to grab. The guild and party tables I'm getting the name of those so that I can display the name instead of ID to the user, and the charlog table has the char_id's creation date and last login date that I want to show as well. Here are the tables:

char table:

----------------------------------------------------
| char_id    | name    | guild_id    | party_id    |
|---------------------------------------------------
| 150000     | char1   | 3           | 3           |
| 150001     | char2   | 2           | (NULL)      |
| 150002     | char3   | (NULL)      | 1           |
| 150003     | char4   | 1           | 2           |
----------------------------------------------------

guild table:

-------------------------
| guild_id   | name     |
-------------------------
| 1          | guild_1  |
| 2          | guild_2  |
| 3          | guild_3  |
-------------------------

party table:

-------------------------
| party_id   | name     |
-------------------------
| 1          | party_1  |
| 2          | party_2  |
| 3          | party_3  |
-------------------------

charlog table:

----------------------------------------------------
| time                 | char_id   | char_msg      |
----------------------------------------------------
| 2015-02-14 06:45:32  | 150000    | make new char |
| 2015-02-14 06:45:58  | 150000    | char select   |
| 2015-02-15 12:32:19  | 150001    | make new char |
| 2015-02-15 16:54:01  | 150000    | char select   |
| 2015-02-15 19:23:54  | 150001    | char select   |
| 2015-02-16 01:32:13  | 150002    | make new char |
| 2015-02-16 01:33:01  | 150003    | make new char |
| 2015-02-16 04:45:43  | 150000    | char select   |
| 2015-02-16 07:43:22  | 150003    | char select   |
----------------------------------------------------

As mentioned, I need to get the make new char entry from the charlog table to display when the character was created and as well the LAST (by date) char select entry to display when the character was played, all of this with one single char_id.

All in all, I'd be looking for a table that looks like this:

---------------------------------------------------------------------------------------------------------------------
| char_id    | name     | guild_id | guild_name | party_id | party_name | create_time         | lastlogin_time      |
---------------------------------------------------------------------------------------------------------------------
| 150000     | char1    | 3        | guild_3    | 3        | party_3    | 2015-02-14 06:45:32 | 2015-02-16 04:45:43 |

I'm using the following active record lines in Codeigniter to attempt to get the data I need. It returns the create_date correctly but the lastlogin_time is not returned (it's blank):

function get_char_info($cid) {
    $this->db->select('char.*,guild.guild_id,guild.name AS guild_name,party.party_id,party.name AS party_name,charlog1.time AS create_time,charlog2.time AS lastlogin_time');
    $this->db->from('char');
    $this->db->where('char.char_id', $cid);
    $this->db->join('guild', 'char.guild_id = guild.guild_id', 'left');
    $this->db->join('party', 'char.party_id = party.party_id', 'left');
    $this->db->join('charlog AS charlog1', 'char.char_id = charlog1.char_id AND charlog1.char_msg = "make new char"', 'left');
    $this->db->join('charlog AS charlog2', 'char.char_id = charlog2.char_id AND charlog2.char_msg = (SELECT max(charlog.time) FROM charlog WHERE char_msg = "char select")', 'left');
    $query = $this->db->get();
    return $query->row();
}

As mentioned, the guild_name, party_name and create_time come through correctly, but the lastlogin_time is blank, no error.

I've tried jumbling some things around in the active record clauses but can't get the lastlogin_time to show. Any help would be appreciated.

Answer 1

The join on your subquery is not correct :

It should be charlog2.time = (SELECT....) not charlog2.char_msg = (SELECT....)