MySQL 过滤连接

Question

这似乎可行，但是告诉显示值不为零的记录，似乎都在告诉它不要显示值为 NULL 的记录。 (即不在表 B 中的记录)。似乎 0 等于 mysql 中的 NULL。如何获得我正在寻找的结果？

SELECT DISTINCT Name, CategoryID
FROM sockcategory 
LEFT JOIN sockcategoryparentxref ON 
sockcategory.CategoryID = sockcategoryparentxref.Category_ID 
WHERE CategoryParentID !=0;

模式/样本数据

表sockcategory:

+-----------+------------+
|   Name    | CategoryID |
+-----------+------------+
| Red       |          1 |
| Blue      |          2 |
| Green     |          3 |
| Colors    |          4 |
| Wholesale |          5 |
+-----------+------------+

表sockcategoryparentxref:

+-------------+------------------+
| Category_ID | CategoryParentID |
+-------------+------------------+
|           1 |                4 |
|           2 |                4 |
|           3 |                4 |
|           4 |                0 |
+-------------+------------------+

期望的输出:

+-----------+------------+
|   Name    | CategoryID |
+-----------+------------+
| Red       |          1 |
| Blue      |          2 |
| Green     |          3 |
| Wholesale |          5 |
+-----------+------------+

Answer 1

如果我没理解错的话，这就是你要找的:

SELECT DISTINCT `Name`, `CategoryID`
FROM `sockcategory` 
LEFT JOIN `sockcategoryparentxref`
    ON `sockcategory`.`CategoryID` = `sockcategoryparentxref`.`Category_ID`
    AND `CategoryParentID` <> 0;