I have four tables, three of which are pretty static: haul\_types, dumpster\_type\_team (the dumpster\_type\_team has the many-to-many relationship between dumpster\_types and teams), and users. The fourth table, hauls, has transactional data.

```
haul_types:

id
name

```

[![haul_types_data](https://i.stack.imgur.com/75MYB.png)](https://i.stack.imgur.com/75MYB.png)

```
dumpster_type_team:

id
dumpster_type_id
team_id

```

[![dumpster_type_team_data](https://i.stack.imgur.com/naRM0.png)](https://i.stack.imgur.com/naRM0.png)

```
users:

id
first_name
last_name
is_driver
team_id

```

[![users_data](https://i.stack.imgur.com/hRdIT.png)](https://i.stack.imgur.com/hRdIT.png)

```
hauls:

haul_type_id
haul_status_id
set_dumpster_type_id
completed_driver_id
team_id

```

[![hauls_data](https://i.stack.imgur.com/ApZGz.png)](https://i.stack.imgur.com/ApZGz.png)

I would like a query that has a combination of dumpster\_types, haul\_types, and drivers (users) and a count of the hauls they were involved in. In some cases, there should be a count of zero because some drivers haven't completed hauls for every haul\_type / dumpster type combination.

Here's the query I have so far that seems to be behaving as if it is an inner join because the records are getting filtered to only show where there are matches:

```sql
SELECT
    c.haul_type_id,
    c.dumpster_type_id,
    c.driver_id,
    count(h.id) AS haul_count
FROM
    hauls h
    RIGHT JOIN ( SELECT DISTINCT
            ht.id AS haul_type_id,
            dtt.dumpster_type_id AS dumpster_type_id,
            dtt.team_id AS team_id,
            u.id AS driver_id
        FROM
            haul_types ht
            CROSS JOIN dumpster_type_team dtt
            CROSS JOIN users u
        WHERE
            u.team_id = dtt.team_id
            AND u.is_driver = TRUE) c ON c.haul_type_id = h.haul_type_id
    AND c.dumpster_type_id = h.set_dumpster_type_id
    AND c.driver_id = h.completed_driver_id
    AND c.team_id = h.team_id
WHERE
    h.team_id = 9
    AND h.haul_status_id = 3
    AND h.completed_driver_id IS NOT NULL
GROUP BY
    c.haul_type_id, c.dumpster_type_id, c.driver_id

```

When I run the subquery in isolation:

```sql
SELECT DISTINCT
            ht.id AS haul_type_id,
            dtt.dumpster_type_id AS dumpster_type_id,
            dtt.team_id AS team_id,
            u.id AS driver_id
        FROM
            haul_types ht
            CROSS JOIN dumpster_type_team dtt
            CROSS JOIN users u
        WHERE
            u.team_id = dtt.team_id
            AND u.is_driver = TRUE

```

I get the results I want: a row for each permutation of haul\_type, dumpster\_type, driver\_id, and team\_id. However, when I run the entire query, I get filtered results despite the right join.

What I would like to have is the following:
If I have 4 haul\_types: delivery, swap, live, pickup

and 2 dumpster\_types: 10YD, 15YD

and 2 drivers: 1, 2

I would like a haul count for the combination of haul\_type, dumpster\_type, and driver. If there are no hauls matching the row, show 0:

[![expected_results](https://i.stack.imgur.com/xsdmS.png)](https://i.stack.imgur.com/xsdmS.png)

Any help is appreciated. Thank you