从MySQL中的多个表中进行选择

时间:2021-02-24 01:30:52

I have two tables namely States, Package_Details. Below are the details of the table.

我有两个表,即States,Package_Details。以下是该表的详细信息。

States

  • state_id
  • state_name

Package_Details

  • id
  • sender_state //Stores state with state ID
  • sender_state //使用状态ID存储状态

  • receiver_state //Stores state with state ID
  • receiver_state //用状态ID存储状态

Now I am having problems constructing a SQL query that will select and display the records in the Package_Details replacing the sender_state and receiver_state with the corresponding state_name in States table.

现在我在构建SQL查询时遇到问题,该查询将选择并显示Package_Details中的记录,将sender_state和receiver_state替换为States表中的相应state_name。

2 个解决方案

#1


You need a join. Example:

你需要加入。例:

SELECT p.id, s1.state_name AS sender, s2.state_name AS receiver
  FROM 
       package_details p
  JOIN states s1 ON (p.sender_state = s1.state_id)
  JOIN states s2 ON (p.receiver_state = s2.state_id)

Note how you can uses states twice in the query by assigning it an alias (states s1) and using the aliases to refer to the table.

请注意如何在查询中使用状态两次,方法是为其指定别名(状态s1)并使用别名来引用表。

If you need to do this join often, you may wish to create a view.

如果您需要经常进行此连接,您可能希望创建一个视图。

MySQL's documentation on joins is here: http://dev.mysql.com/doc/refman/5.0/en/join.html. The documentation on creating views is here: http://dev.mysql.com/doc/refman/5.0/en/create-view.html.

关于连接的MySQL文档在这里:http://dev.mysql.com/doc/refman/5.0/en/join.html。有关创建视图的文档,请访问:http://dev.mysql.com/doc/refman/5.0/en/create-view.html。

#2


You'll want to read up on SQL JOINs:

您将要阅读SQL JOIN:

http://www.w3schools.com/Sql/sql_join.asp

http://dev.mysql.com/doc/refman/5.0/en/join.html

#1


You need a join. Example:

你需要加入。例:

SELECT p.id, s1.state_name AS sender, s2.state_name AS receiver
  FROM 
       package_details p
  JOIN states s1 ON (p.sender_state = s1.state_id)
  JOIN states s2 ON (p.receiver_state = s2.state_id)

Note how you can uses states twice in the query by assigning it an alias (states s1) and using the aliases to refer to the table.

请注意如何在查询中使用状态两次,方法是为其指定别名(状态s1)并使用别名来引用表。

If you need to do this join often, you may wish to create a view.

如果您需要经常进行此连接,您可能希望创建一个视图。

MySQL's documentation on joins is here: http://dev.mysql.com/doc/refman/5.0/en/join.html. The documentation on creating views is here: http://dev.mysql.com/doc/refman/5.0/en/create-view.html.

关于连接的MySQL文档在这里:http://dev.mysql.com/doc/refman/5.0/en/join.html。有关创建视图的文档,请访问:http://dev.mysql.com/doc/refman/5.0/en/create-view.html。

#2


You'll want to read up on SQL JOINs:

您将要阅读SQL JOIN:

http://www.w3schools.com/Sql/sql_join.asp

http://dev.mysql.com/doc/refman/5.0/en/join.html