mysql 在 php 中使用空值左连接
示例场景；假设我正在寻找员工及其雇主
tblEmployee
eid | name
1 | james
2 | harry
tblCompany
cid | name
1 | ABC Ltd
2 | XYZ Corp
tblMappings
mid | eid | cid
1 | 1 | 2
James 被分配到 XYZ Corp 公司，但 Harry 没有分配。我想把所有员工都撤出来，不管他们有没有被分配；
SELECT * FROM `tblEmployee`
LEFT JOIN `tblMappings` ON
`tblEmployee`.`eid` = `tblMappings`.`eid`
这将在 phpMyAdmin 中返回以下内容；
eid | name | mid | eid | cid
1 | james | 1 | 1 | 2
2 | harry | NULL | NULL | NULL
我正在 PHP 中执行查询并使用：
while($row=mysql_fetch_assoc($results)) { ...blah.... }
但是对于 harry $row['eid'] 是空白的。我不知道如何解决这个问题，因此任何观点都将不胜感激。非常感谢:)
Example scenario; lets say I'm searching for employees and their employers
tblEmployee
eid | name
1 | james
2 | harry
tblCompany
cid | name
1 | ABC Ltd
2 | XYZ Corp
tblMappings
mid | eid | cid
1 | 1 | 2
James is assigned to the company XYZ Corp, but harry has no assignment. I want to pull out all employees whether they are assigned or not;
SELECT * FROM `tblEmployee`
LEFT JOIN `tblMappings` ON
`tblEmployee`.`eid` = `tblMappings`.`eid`
This returns the following in phpMyAdmin;
eid | name | mid | eid | cid
1 | james | 1 | 1 | 2
2 | harry | NULL | NULL | NULL
I'm performing the query in PHP and using:
while($row=mysql_fetch_assoc($results)) { ...blah.... }
But for harry $row['eid'] is blank. I'm not sure how to work this one out so any points would be greatly apprecited. Many thanks :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
tblEmployee中的eid字段不为空，但tblMappings中的eid字段为空，这是预期的因为该表中没有匹配的记录。指定您需要哪些字段而不是SELECT * ...。The
eidfield fromtblEmployeeisn't empty, but theeidfield fromtblMappingsis empty, which is expected since there's no matching record in that table. Specify which fields you want instead ofSELECT * ....tblEmployee和tblMappings还包含列eid，当然，tblMappings 中的第二个 eid 会覆盖第一个 eid。
为了解决这个问题，您可以在返回列中指定表名
Both
tblEmployeeandtblMappingsalso contains columneid,and naturally the second eid from
tblMappingsis override the first one.To solve this, you can specify the table name in the return columns
您不应该使用
SELECT *。这是一个坏习惯。相反，请考虑一下：
You should not be using
SELECT *. It's a bad habit.Instead, consider this: