How to efficiently execute sql query of child records from multiple tables?

Question

We have a simple database. The user table contains users. In the Accounts table, there are several accounts for each user. The topic table contains several topics for each account.

So, the user will have several accounts, and each account will have several topics. So, if I have a user with id = 1, how can I effectively query all 3 tables to get all the accounts and topics for that user?

I am currently using foreach loops that trigger a lot of sql queries. Is there a way to run a single sql query to get what I want?

Here is the code I'm using right now (which is CodeIgniter code):

$data=array();
$accounts=$this->db->get_where('accounts',array('user_id'=>1));
foreach ($accounts->result() as $account) {
    $tmp=array();
    $topics=$this->db->get_where('topics',array('account_id'=>$account->id));
    foreach ($topics->result() as $topic) {
        $this->db->order_by($order_by);
        $terms=$this->db->get_where('terms',array('topic_id'=>$topic->id));
        array_push($tmp,array('topic'=>$topic, 'terms'=>$terms->result()));
    }
    array_push($data,array('account'=>$account, 'topics'=>$tmp));
}
return $data;

Answer 1

.

User- > Many Accounts

Account- > Many Topic

, ( , Jon Doe). , ( , , Acme 1 Acme 2 Jon Doe). , , . . , .

SELECT
   u.UserID,
   a.Account
   t.Topic
FROM 
    Users u
INNER JOIN 
     Accounts a
ON u.UserID = a.UserID
INNER JOIN
     Topics t
ON t.AccountID = a.AccountID

, WHERE:

SELECT
   u.UserID,
   a.Account
   t.Topic
FROM 
    Users u
INNER JOIN 
     Accounts a
ON u.UserID = a.UserID
INNER JOIN
     Topics t
ON t.AccountID = a.AccountID
WHERE u.UserID=1

Answer 2

SELECT top.`topic_id` [etc]
FROM `accounts` acc
JOIN `topics` top ON (top.`account_id` = acc.`id`)
WHERE acc.`member_id` = 1

, CI.

, :

SELECT usr.`id`, acc.`account_id`, top.`topic_id` [etc]
FROM `users` usr
JOIN `accounts` acc ON (acc.`member_id` = usr.`id`)
JOIN `topics` top ON (top.`account_id` = acc.`id`)
WHERE usr.`id` = 1