I'm coding my first Joomla! 4 component. There are two tables for the component: *pfx\_list*, and *pfx\_member\_to\_list*. Table *pfx\_list* hash primary key *id*, and table *pfx\_member\_to\_list* has foreign key *list\_id* pointing to table *pfx\_list*.

I need a list of all rows of *pfx\_list* joined with the number of matches in *pfx\_member\_to\_list*, which can be none, one, or many.

In the first version, there was a simple LEFT JOIN, but this did (of course) not return rows with zero entries in the right table. *But*, this version was running fine (except for the missing rows), so I know the component code is basically ok.

I then changed the query to a LEFT JOIN on a subselect, so that rows with no matches in the right table will also be returned. This is the query, and when run through php\_myadmin, it returns the desired result:

```
SELECT a.id, a.list_name, coalesce( member_cnt, 0 )
FROM      pfx_list as a
LEFT JOIN ( select list_id, count(*) as member_cnt 
            from pfx_member_to_list
            group by list_id
            ) b
        ON a.id = b.list_id
GROUP BY a.list_name
ORDER BY a.list_name;   

```

I'm stuck with translating this to a JDataBase query object. Here is the current code:

```
$db       = $this->getDbo();
$query    = $db->getQuery(true);
$subquery = $db->getQuery(true);

$subquery->select(['list_id', 'count(*) as member_cnt'])
         ->from($db->quotename('#__member_to_list', 'b'))
         ->group($db->quotename('b.list_id'));

$query->select(['a.id', 'a.list_name', 'a.nz_list_id', 'a.nz_list_key', 'coalesce(member_cnt, 0)'])
      ->from($db->quoteName('#__list', 'a'))
      ->join('LEFT', $subquery . ' ON ' . $db->quoteName('a.id') . ' = ' . $db->quoteName('b.list_id'))
      ->order($db->quoteName('a.list_name') . ' ASC')
      ->group($db->quotename('a.list_name'));

```

When the query is run in Joomla! via admin menu entry, I'm getting error:

```
An error has occurred.
500 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version 
for the right syntax to use near 'SELECT list_id,count(*) as member_cnt FROM 
`pfx_member_to_...' at line 4 You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near 'SELECT list_id,count(*) as 
member_cnt FROM ``pfx_member_to_...' at line 4 

```

I've tried removing `quoteName()`, and some more changes, but cannot seem to find out what is wrong.

**Edit 1**: Here is a [working sample on db-fiddle.com](https://www.db-fiddle.com/f/ssXoXNNop6oa3zmVuswRNY/2)