php 如何从两个表中的多个列中选择数据，其中两个表都有一个公共列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19172022/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how do i select data from multiple columns from two tables where there is a common column in both?
提问by leoarce
seems like a cake walk, because of the common column thing, but i've looked and i've looked and can't find the answer anywhere. there may be very similar things people are trying to do, but everyone always needs it slightly different than how i need it. anyway here goes...
由于常见的列问题，这似乎很容易，但我已经看了又看了，但在任何地方都找不到答案。人们可能会尝试做非常相似的事情，但每个人都需要它与我需要它的方式略有不同。反正这里是...
jobs table
工作表
+----+--------+----+----+----+
| id | userid | i1 | i2 | i3 |
+----+--------+----+----+----+
| 1 | 1 | a | k | t |
| 2 | 1 | b | l | u |
| 3 | 1 | c | m | v |
| 4 | 2 | d | n | w |
| 5 | 2 | f | o | x |
| 6 | 2 | g | p | y |
| 7 | 3 | h | q | z |
| 8 | 3 | i | r | a |
| 9 | 4 | j | s | b |
+----+--------+----+----+----+
user_table table
用户表
+--------+----+----+----+
| userid | fn | ln | i4 |
+--------+----+----+----+
| 1 | a | b | w |
| 2 | c | d | x |
| 3 | e | f | y |
| 4 | g | h | z |
+--------+----+----+----+
i want to select multiple columns from one table and multiple columns from another table, and the amount of columns don't have the same amount. so if i'm selecting id, i1, i2, and i3 from jobs table and selecting fn, ln, and i4 from user_table table, then that means i'm selecting 4 pieces of info from jobs table and 3 pieces of info from user_table table.
我想从一个表中选择多个列，从另一个表中选择多个列，并且列的数量不相同。因此，如果我从作业表中选择 id、i1、i2 和 i3 并从 user_table 表中选择 fn、ln 和 i4，那么这意味着我从作业表中选择了 4 条信息，从 user_table 中选择了 3 条信息桌子。
so let's say i want to select a specific job and display the info for that job, but also i want to display the info of the user that belongs to the job then it might go something like this...
所以假设我想选择一个特定的工作并显示该工作的信息，但我也想显示属于该工作的用户的信息，然后它可能会像这样......
job 1:
工作1：
id: 1, i1: a, i2: k, i3: t, fn: a, ln: b, i4: w
id: 1, i1: a, i2: k, i3: t, fn: a, ln: b, i4: w
for job 4 it would be:
对于工作 4，它将是：
id: 4, i1: d, i2: n, i3: w, fn: c, ln: d, i4: x
id: 4, i1: d, i2: n, i3: w, fn: c, ln: d, i4: x
the jobs table and user_table table have the common column of userid. so how do i write my query to do the above using this common column?
作业表和 user_table 表具有 userid 的公共列。那么我如何编写查询以使用此公共列执行上述操作？
i tried all sorts of things with SELECT and WHERE and AND and JOIN and UNION and AS and GROUP and BY and writing the table names separate from the column names and writing the table names and column names together with a period in between. i just can't find the correct way to write this query.
我尝试了 SELECT、WHERE、AND、JOIN、UNION、AS、GROUP 和 BY 的各种方法，并将表名与列名分开，并将表名和列名写在一起，中间有一个句点。我只是找不到编写此查询的正确方法。
in these examples below that don't work i'm just trying to select one thing from the user_table table and everything from the jobs table that isn't the userid, but i will need the ability to select multiple things from the 2nd table if needed. so i want to select everything from first table and just certain things from second table.
在下面这些不起作用的示例中，我只是想从 user_table 表中选择一件事，从工作表中选择不是用户 ID 的所有内容，但我需要能够从第二个表中选择多个内容，如果需要。所以我想从第一个表中选择所有内容，从第二个表中选择某些内容。
doesn't work:
不起作用：
SELECT id, i1, i2, i3, i4 FROM jobs, user_table WHERE jobs.id = $id GROUP BY id
doesn't work:
不起作用：
SELECT * from jobs where id = $id UNION SELECT i4 from user_table where userid = $userid
回答by Nick Orlando
The query
查询
SELECT * FROM jobs
INNER JOIN user_table on jobs.userid = user_table.userid
will join your jobs information with the user information. Then just add your where clause:
将您的工作信息与用户信息结合起来。然后只需添加您的 where 子句：
SELECT * FROM jobs
INNER JOIN user_table on jobs.userid = user_table.userid
where jobs.id = $id
回答by immulatin
You want to join the tables like so:
你想像这样加入表：
SELECT j.column1, j.column2, u.column5, u.column6
FROM
jobs j
INNER JOIN
user_table u
ON
u.userid = j.userid
WHERE
j.id = $id
回答by Scary Wombat
try
尝试
SELECT id, i1, i2, i3, i4 FROM jobs
inner join user_table on jobs.id = user_table.id WHERE jobs.id = $id GROUP BY id
