mysql连接第3个表，其中FK没有直接链接

I have 3 tables,

One table have data with FK of 2nd table, and 2nd table have FK of 3rd table.

Now i want to get data of 1st table by joining 3rd table, but 1st table and 3rd table are not linked directly,

Below is my model of codeigniter,

public function openinfo($openid)
{
    $this->db->join('sc_users', 'sc_users.user_id = sc_class.user_id','INNER');
    $this->db->join('sc_companies', 'sc_companies.company_id = sc_class.company_id','LEFT');
    $this->db->where('sc_class.open_id', $openid);
    return $this->db->get('sc_class')->row();
}

sc_users have FK named sc_usergroupid which is PK of table called sc_usergroups,

When above model function run, i want to get value of sc_usergroupname which is column of sc_usergroups table.

In short, this is 3N data tables, and want to fetch record, I can easily join multiple table with many join but reference to 3rd table. How to get that,

Thanks,

Your question isn't very clear because you haven't specified the name and description of the third table. Is the second table really sc_usergroups and the third table really sc_usergroup as you have in your question? That seems a little strange, but based on that I made the MySQL query below. The idea is that in the WHERE clause you select the user from table 1 then you connect 1 to 2, then 2 to 3, and then you request values from 1 and 3 in the SELECT part of the statement.

SELECT su.name, sug.name 
FROM sc_users su, sc_usergroups sugs, sc_usergroup sug 
WHERE 
  su.user_id = 1 AND 
  su.sc_usergroupid = sugs.usergroupsid AND 
  sugs.sug_usergroupid = sug.usergroupid