I have the following two tables:


id  name
1   Child1
2   Parent1
3   Parent2
4   Child2
5   Parent3
6   Child3


id  child  parent
1      1         2
2      1         3
3      4         5

And I successfully get the list of records like:

Childname  Parents
Child1     Parent1, Parent2
Child2     Parent3

by using the following sql-query:

$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query = 'SELECT
  GROUP_CONCAT( c.`name` ORDER BY c.`name` SEPARATOR ', ') AS parents
FROM `#__parents` AS b
INNER JOIN `#__records` AS a ON a.`id`=b.`child`
INNER JOIN `#__records` AS c ON c.`id`=b.`parent`
GROUP BY b.`child`;

The problem is I get only records that have some parent(s) so I don't get Child3 with ID 6 in my list for it has no any parent relation. Could you alter my query to get ALL the records regardless of they have the parent(s) or not?

  • JFactory::getDBO() means that I need SQL for Joomla. I was asking such questions for many times and this is the first one when it's off-topic – stckvrw Aug 5 '15 at 3:45
    c.`name` AS childName,
    GROUP_CONCAT(p.`name`) AS parents
    records AS c
LEFT JOIN parents ON c.id = parents.child
LEFT JOIN records AS p ON parents.parent = p.id


| improve this answer | |
  • 1
    Could you possibly how the OP is wrong what what you've done differently in your code. It may work, but helping them learn is a bonus ;) – Lodder Aug 4 '15 at 20:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.