mysql左连接不返回所有左表行 [英] mysql left join not return all left table row

问题描述

我有两张桌子

phonetype:
phonetypeID (PK)
phonetyepe

phone:
phoneID (PK)
peopleID (fK)
phonetypeID (fK)
areacode
number`

对于特定的 peopleID，我想返回所有电话类型，如果 peopleID 有号码，则显示它，如果不是空的.类似的东西:

For a specific peopleID I would like to return all the phonetypes and if the peopleID has the number diplay it, if not empty. Something like:

phonetype    phonetypeID    areacode    number
company      1              111         11111
fax          2
home         3              222         222222
mobile       4

这是我准备好的声明:

$stmt = $conn->prepare("SELECT *
FROM phonetype
LEFT JOIN phone
ON phonetype.phonetypeID=phone.phonetypeID
where phone.peopleID = ?; 
");
if ( !$stmt ) {die(printf("Error: %s.\n", mysqli_stmt_error($stmt) ) );}
else if ( !$stmt->bind_param('i', $peopleID) ) {die(printf("Error: %s.\n", mysqli_stmt_error($stmt) ) );}

但这样我只有

phonetype    phonetypeID    areacode    number
company      1              111         11111
home         3              222         222222

我做错了什么?PS 这是我第一次加入！！谢谢！！

What am I doing wrong? PS this is my first join!! Thanks!!

编辑(此问题遵循另一个问题 [以特定顺序显示数据库中的数据:php or mysql? .阅读Gravel、Robbie Averill、Maximus2012和Miken32的答案后，我明白了有一个专用表是更好的设计电话类型，并在电话表中使用外键.认为这是一个完全不同的场景，我问了这个新问题.如果有人认为这是重复的并且有办法加入第2个问题，我会这样做.再次感谢所有花时间帮助我的人.)

EDIT (This question follows this other question [display data from database in a specific order: php or mysql? . After reading the answers of Gravel, Robbie Averill, Maximus2012 and Miken32 I understood that it was a better design to have a dedicate table phonetype, and use a foreign key in the phone table. Thinking that this was a completely different scenario, I asked this new question. If anybody thinks this is a duplicate and there is a way to join the 2 question, I will do it. Thanks again to everybody who took the time to help me.)

推荐答案

在您的查询中；

SELECT *
FROM phonetype
LEFT JOIN phone
ON phonetype.phonetypeID=phone.phonetypeID
where phone.peopleID = ?

...WHERE 子句在最右边的表上带有条件，将删除最右边的表没有值的所有行，否定 LEFT JOIN.

...the WHERE clause with a condition on the rightmost table will remove all rows where the rightmost table has no value, negating the LEFT JOIN.

您最可能想要的是将条件添加到 LEFT JOIN 的 ON 子句中，允许 phone 的空值仍然出现;

What you most likely want is to add the condition to the LEFT JOIN's ON clause instead, allowing the empty values of phone to still show up;

SELECT *
FROM phonetype
LEFT JOIN phone
  ON phonetype.phonetypeID=phone.phonetypeID
 AND phone.peopleID = ?

这篇关于mysql左连接不返回所有左表行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！