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
- receiver_state //Stores state with state ID
sender_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。
#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。