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

Question

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,

Answer 1

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