0

I have the following two tables:

#__records:

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

#__parents:

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
  b.`child`,
  a.`name`,
  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`;
$db->setQuery($query);

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?

closed as off-topic by Anibal, Rene Korss, fruppel, David Fritsch Aug 5 '15 at 3:34

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "This question does not appear to be about Joomla! administration and development, within the scope defined in the help center" – Anibal, Rene Korss, fruppel, David Fritsch
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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
2
SELECT
    c.`name` AS childName,
    GROUP_CONCAT(p.`name`) AS parents
FROM
    records AS c
LEFT JOIN parents ON c.id = parents.child
LEFT JOIN records AS p ON parents.parent = p.id
GROUP BY
    c.id

http://sqlfiddle.com/#!9/3df96/2

  • 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.