如何从两个不同的表中选择记录 - How to select record from two different tables

How to select record from two different tables

本文关键字：记录选择两个 | 更新日期: 2024-03-07

我有两个表，它们是

+----------------------------+
| task                       |
+----------------------------+
| id | t_title | t_assign_to |
+----------------------------+

+-----------------------------+
| task_employee               |
+-----------------------------+
| id | emp_name | emp_underon |
+-----------------------------+

我的问题是，我想从具有外键（t_assign_to）的task_employe表中emp_underon="2"的任务表中获取t_assign_to

<?php                
  $sql = mysql_query("SELECT * FROM task");/// i got all value from here
  $sql=mysql_query("SELECT * FROM task_employee WHERE emp_underon ='2'");// i got all value from here
  //$sql=mysql_query("SELECT * FROM task UNION SELECT * FROM task_employee WHERE emp_underon='2'");
  $count = mysql_num_rows($sql);
  while($row=mysql_fetch_assoc($sql))
  {
    echo $row['t_assign_to'];
  }
?>

我认为t_assign_to是task_employe的外键所以我把他们一起加入

模式1

$sql = "SELECT * FROM task t INNER JOIN task_employee te ON t.t_assign_to = te.id";

模式2

$sql = "SELECT * FROM task t,task_employee te WHERE t.t_assign_to = te.id";

如果表中的字段名称与其他表中的其他字段相同，只需更改字段名称，如：

$sql = "SELECT table.field as new_field_name FROM task t,task_employee te WHERE t.t_assign_to = te.id";

为了您的方便，

$sql = "SELECT * FROM task t INNER JOIN task_employee te ON t.t_assign_to = te.id WHERE te.emp_underon = '2' "
$q = mysql_query($sql);
$c = mysql_num_rows($q);
if($c > 0)
{
  while($r = mysql_fetch_assoc($q))
  {
    echo $r['t_assign_to'];
  }
}

虽然我没有测试这个SQL，但我希望你能通过这个查询得到答案。

select task.t_title, task.t_assign_to, task_employee.id as emp_id, task_employee.emp_name from task inner join task_employee on task.t_assign_to = task_employee.emp_underon where  task_employee.emp_underon = 2