php - MySQL JOIN 将 PHP 变量传递给两个表

Question

我在 PHP 中有以下 MySQL 查询，它传递一个变量来完成查询:

SELECT * from mobile_tech WHERE uid=$uid  order by timestamp DESC limit 0,1

我有以下 MySQL JOIN，它提供来自两个表的数据:

SELECT mobile_tech.latitude, mobile_tech.longitude, mobile_tech.timestamp, mobile_tech.uid, gbl_qemplisting.EmpNo, gbl_qemplisting.FirstName, gbl_qemplisting.LastName 
FROM mobile_tech, gbl_qemplisting
WHERE mobile_tech.uid=gbl_qemplisting.EmpNo AND date(timestamp)='$currentday' 
group by uid

我需要使用 JOIN 将这两个查询合并为一个，并仍然传递 $uid 变量来完成查询。

我尝试了以下方法，但没有成功:

SELECT mobile_tech.latitude, mobile_tech.longitude, mobile_tech.timestamp, mobile_tech.uid, gbl_qemplisting.EmpNo, gbl_qemplisting.FirstName, gbl_qemplisting.LastName 
FROM mobile_tech, gbl_qemplisting
WHERE mobile_tech.uid=$uid AND gbl_qemplisting.EmpNo=$uid AND date(timestamp)='$currentday'

Answer 1

您的查询将返回 $uid 的 mobile_tech 和 gbl_emplisting 行之间的叉积。如果您只需要一行(如第一个查询中所示)，请类似地使用 ORDER BY 和 LIMIT。

SELECT mobile_tech.latitude, mobile_tech.longitude, mobile_tech.timestamp, mobile_tech.uid, gbl_qemplisting.EmpNo, gbl_qemplisting.FirstName, gbl_qemplisting.LastName 
FROM mobile_tech, gbl_qemplisting
WHERE mobile_tech.uid=$uid AND gbl_qemplisting.EmpNo=$uid AND date(timestamp)='$currentday'
ORDER BY mobile_tech.timestamp
LIMIT 1