Two left join in one query to Joomla database

Question

I have two tables: #__records and #__parents. In the first table some records may have parent records that are stored in the same table but relations between childs and parents are stored in the second table. For example

#__records:

id  name
1   Child1
2   Child2
3   Parent1

#__parents

id  child parent
1     2    3
2     1    3

Now I have the query:

$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query->select('a.id, a.name')
    ->from('#__records as a');
$query->select('b.parent as parentid');
$query->join('LEFT', '#__parents AS b ON b.child = a.id');
$db->setQuery($query);
return $query;

It returns the names of records (a.name) but with IDs of parents (b.parent). I need the second "left join" or another way to get the names of parents instead of their IDs. Something like:

select c.name as parentname from #__records as c where c.id=b.parent

Answer 1

$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query->select('a.id, a.name, c.id as parent_id, c.name as parent_name')
    ->from('#__records as a');
$query->select('b.parent as parentid');
$query->join('LEFT', '#__parents AS b ON b.child = a.id');
$query->join('LEFT', '#__records AS c ON b.parent = c.id');
$db->setQuery($query);
return $query;

You definitely can do multiple left joins. Just alias the table to a different alias ('c' in this case) and then have the select also alias the columns they aren't the same as what you are pulling for the child.

Answer 2

If you need to do "many to many" connections it's better to catch table A and B into PHP and then do the operators, but if you need only if A table is relative to C based on B table, the other answer is good ; )