Two aliases for the same MySQL table

Here's how to establish database relationships between connected entities. In this example we'll use a highways map. The final query will show city names and highway that connects them in  result set rows.

Map of highways:

Connections between cities

MySQL tables that represent the picture above:

MySQL tables

Query

SELECT 
 c1.name,
 highway_name,
 c2.name
FROM c_conn c
INNER JOIN c_cities c1 ON c.start_city_id = c1.id
INNER JOIN c_cities c2 ON c.end_city_id = c2.id

and the result

name	highway_name	city
albukerky	h23	boston
boston	h24	cornel
cornel	h25	dallas