从mysql JOIN获取所有结果，即使没有连接匹配

I am doing a JOIN operation between a table and itself. The table schema is something like:

我在表和它自己之间进行JOIN操作。表模式类似于:

| id | name | parent |
|  0 | .... |   ...  |
|  1 | .... |   ...  |
|  2 | .... |   ...  |

The query looks like:

查询看起来像:

$qMarks = str_repeat('?,', count($arr) - 1) . '?';
$stmt = $db->prepare("SELECT t1.id AS t1id, t1.name AS t1name, t2.name AS t2name 
                            FROM cats t1 
                            JOIN cats t2 ON t1.parent = t2.id
                            WHERE t1.name IN ($qMarks)");
$stmt->execute($arr);
$result = $stmt->fetchAll();

So, I'm passing in an array of names $arr, and I am getting back the rows where there is a matching name to one of the items in the parameter array. This works fine, so long as there is also a matching id somewhere for parent.

所以,我传入了一个名为$ arr的数组,并且我回到了与参数数组中的一个项具有匹配名称的行。这样可以正常工作,只要父母在某处也有匹配的id。

But, sometimes the value for parent will be blank (empty cell). I still want to get those results, as long as the t1.name IN ($qMarks) condition is met.

但是,有时父级的值将为空(空单元格)。只要满足t1.name IN($ qMarks)条件,我仍然希望获得这些结果。

How do I return the values, even if the t1.parent value in the table is blank?

即使表中的t1.parent值为空,如何返回值?

1 个解决方案

#1

Use a left join.

使用左连接。

$stmt = $db->prepare("SELECT t1.id AS t1id, t1.name AS t1name, t2.name AS t2name 
                        FROM cats t1 
                        LEFT JOIN cats t2 ON t1.parent = t2.id
                        WHERE t1.name IN ($qMarks)");