即使内连接不匹配，如何返回内容

Question

i have two tables

Cursos

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| curso | varchar(255)     | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

Trienios

+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| data_trienio | varchar(255)     | NO   |     | NULL    |                |
| curso_id     | int(11)          | NO   |     | NULL    |                |
| oe_id        | int(11)          | NO   |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

those tables are connected through a relationship (as seen through curso_id), and i want to make a query where i retrieve the curso records and the number of trienio records related to each one of them

so i've done this query in laravel

$curso = Curso::select([
            'cursos.curso',
            \DB::raw('count(trienios.curso_id) as count')
        ])->join('trienios', 'trienios.curso_id', '=', 'cursos.id')
        ->groupBy('trienios.curso_id');

which translates to this

select `cursos`.`curso`,
       count(trienios.curso_id) as count
from `cursos`
inner join `trienios`
    on `trienios`.`curso_id` = `cursos`.`id`
group by `trienios`.`curso_id`

and it gets me the number of cursos with trienios related to them. HOWEVER, it only gives those who have a number of trienios related to them. the other ones who dont have trienios related to them are not queried, and i want to query them. so, how do i solve this issue ?

Answer 1

Use leftJoin(), select cursos.id, and group by cursos.id:

$curso = Curso::select([
        'cursos.id',
        DB::raw('count(trienios.curso_id) as count')
    ])->leftJoin('trienios', 'trienios.curso_id', '=', 'cursos.id')
    ->groupBy('cursos.id');

This is the query I want to run:

SELECT c.id,
       COUNT(t.curso_id) AS count
FROM cursos c
LEFT JOIN trienios t
    ON t.curso_id = c.id
GROUP BY c.id

The LEFT JOIN should prevent records from cursos from being dropped even if they have no matching counterparts in trienios. As for the error you were seeing, you were selecting cursos.curso, which is not an aggregate and which does not appear in your GROUP BY clause. When MySQL is in only_full_group_by this is not allowed, hence I changed the select clause to cursos.id.