The subject of my question is a bit confusing because I'm running out of words to suit the general and technical description of it.

In a less technical terms, what I'm trying to accomplish specifically is:

> 
> List all churches and their corresponding booking status from a service they are offering with the following fields:
> 
> 
> 

* Church id **AS** `id`
* Church name **AS** `name`
* Count of `pending` status from all services they are offering **AS** `pendingCount`
* Count of `fully booked` status from all services they are offering **AS** `bookedCount`

The structure of associate tables are as follows:

Church
------

```
+---------+---------------+
| id(int) | name(varchar) |
+---------+---------------+

```

Services
--------

```
+---------+---------------+---------------+
| id(int) | name(varchar) | churchId(int) |
+---------+---------------+---------------+

```

Bookings
--------

```
+---------+-----------------+----------------+
| id(int) | status(varchar) | serviceId(int) |
+---------+-----------------+----------------+

```

What I have come up so far is this, which I'm totally have no idea why it is compiling but produces a negative result:

```
SELECT
    churches.id,
    churches.name,
    pending.count AS pendingCount,
    booked.count AS bookedCount
FROM
    churches
INNER JOIN
    services
ON
    services.churchId = churches.id
LEFT JOIN
    (SELECT
        COUNT(bookings.id) AS `count`,
        bookings.serviceId
     FROM
        bookings
     WHERE
        bookings.status = 'pending'
     GROUP BY
        bookings.serviceId)
     AS pending
ON
    pending.serviceId = services.id
LEFT JOIN
    (SELECT
        COUNT(bookings.id) AS `count`,
        bookings.serviceId
     FROM
        bookings
     WHERE
        bookings.status = 'fully booked'
     GROUP BY
        bookings.serviceId)
     AS booked
ON
    booked.serviceId = services.id

```

Sample output
-------------

[![enter image description here](https://i.stack.imgur.com/Z2uUH.png)](https://i.stack.imgur.com/Z2uUH.png)