How to efficiently sql query child records from multiple tables?

Question

We have a simple database. The User table holds users. The Accounts table holds multiple accounts for each user. The Topics table holds multiple topics for each account.

So, a user would have multiple accounts and each account would have multiple topics. So, if I have a user with id=1 how do I efficiently query all 3 tables to get all the accounts and topics for that user?

I'm currently using foreach loops that run many sql queries. Is there a way to just run one sql query to get what I want??

Here's the code I'm currently using (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

Simply a one to many with another one to many.

User->Many Accounts

Account->Many Topic

Think of your table of Users that one row is unique (contains one user say Jon Doe). Think of your accounts table referencing some sort of user (that is multiple accounts can contain the same user, in addition, account Acme 1 and Acme 2 both pertain to user Jon Doe). Finally, think of your topics table containing a reference to an account. That is each topic has an account id. So that means accounts have many topics associated with them.

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

If you want to narrow in on one user just add a WHERE clause:

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

Is the basic query, not sure about CI.

if you need other info from the member id then:

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