MySql: Selecting from multiple tables

Question

Hello guys, i dont know how to go about this but i need urgent assistance from you guys.

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

States

• state_id
• state_name

Package_Details

• id
• sender_state //Stores state with state ID
• receiver_state //Stores state with 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.

Thanks very much for your time.

Answer 1

You'll want to read up on SQL JOINs:

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

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

Answer 2

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.

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.