SQL 从三个表中选择数据？

Select data from three tables?

提问by sadi

How can I write a SQL statement to select data from three tables?

如何编写SQL语句从三个表中选择数据？

回答by Jim

Use a join

使用连接

SELECT *
FROM table_1
JOIN table_2 ON (table_2.table_1_id = table_1.table_1_id)
JOIN table_3 ON (table_3.table_1_id = table_1.table_1_id)

This will require that each table has a table_1_id key and that there is an entry in each table.

这将要求每个表都有一个 table_1_id 键，并且每个表中都有一个条目。

You can use a LEFT JOIN if table_2 or table_3 may not have data but you still want to show the data from table_1

如果 table_2 或 table_3 可能没有数据，但您仍想显示 table_1 中的数据，则可以使用 LEFT JOIN

回答by Bob Jarvis - Reinstate Monica

Here's a very general example:

这是一个非常普遍的例子：

SELECT KEYVAL, A.OTHER_COL, B.ANOTHER_COL, C.YET_ANOTHER_COL
  FROM TABLE_A A
  INNER JOIN TABLE_B B
    USING (KEYVAL)
  INNER JOIN TABLE_C C
    USING (KEYVAL)

If your database doesn't support USING you could say:

如果您的数据库不支持 USING，您可以说：

SELECT A.KEYVAL, A.OTHER_COL, B.ANOTHER_COL, C.YET_ANOTHER_COL
  FROM TABLE_A A
  INNER JOIN TABLE_B B
    ON (B.KEYVAL = A.KEYVAL)
  INNER JOIN TABLE_C C
    ON (C.KEYVAL = A.KEYVAL)

Or you could say

或者你可以说

SELECT A.KEYVAL, A.OTHER_COL, B.ANOTHER_COL, C.YET_ANOTHER_COL
  FROM TABLE_A,
       TABLE_B,
       TABLE_C
  WHERE B.KEYVAL = A.KEYVAL AND
        C.KEYVAL = A.KEYVAL

but I think the latter is less clear.

但我认为后者不太清楚。

Share and enjoy.

分享和享受。

回答by squillman

If your tables have the same schema and you want to essentially concatenate the rows from each table then you should think about a UNIONquery.

如果您的表具有相同的架构，并且您想从本质上连接每个表中的行，那么您应该考虑一个UNION查询。

SELECT
Field1, Field2, Field3
FROM Table1
UNION
SELECT
Field1, Field2, Field3
FROM Table2
UNION
SELECT
Field1, Field2, Field3
FROM Table3

If you want to include related data from each table in a single row of your result set then you should look at using INNER JOINs and / or OUTER JOINs.

如果您想将每个表中的相关数据包含在结果集中的一行中，那么您应该考虑使用INNER JOINs 和/或OUTER JOINs。

SELECT Table1.Field1, Table2.Field2, Table3.Field3
FROM Table1
INNER JOIN Table2 ON Table1.Field1=Table2.Field1
LEFT OUTER JOIN Table3 on Table1.Field3=Table3.Field3

We'll need more information from you, though, in order to give you a definitive answer including which RDBMS you're using.

不过，我们需要您提供更多信息，以便为您提供明确的答案，包括您使用的是哪个 RDBMS。

回答by Kipotlov

Look at the Join(SQL)clause.

查看Join(SQL)子句。

You can join your first table with your second. Then you third table to your first or second table.

您可以将第一张桌子与第二张桌子一起加入。然后你第三张桌子到你的第一张或第二张桌子。

Ex:

前任：

SELECT * 
FROM TABLE_A
JOIN TABLE_B ON TABLE_A.ID = TABLE_B.ID
JOIN TABLE_C ON TABLE_C.ID = TABLE_B.ID

回答by S.Verma

Using join you can select data from three tables

使用 join 可以从三个表中选择数据

SELECT column_name(s)
FROM table1
JOIN 
  table2 ON table1.column_name = table2.column_name
JOIN 
  table3 ON table3.column_name = table2.column_name;

回答by Md Shamshad

select Data From Three Table like Joins on Country State and city

从三个表中选择数据，如国家和城市的联接

select country.country,country.countryid,countrystate.state, countrystatecity.city from country inner join countrystate on(country.countryid=countrystate.countryid) inner join countrystatecity on(countrystate.stateid=countrystatecity.stateid)

选择 country.country,country.countryid,countrystate.state, countrystatecity.city 从 country 内连接 countrystate on(country.countryid=countrystate.countryid) 内连接 countrystatecity on(countrystate.stateid=countrystatecity.stateid)