One question:
I have two tables:
Table station has the columns stationid and fullname.
Table route has the columns routeid, fromstationid, tostationid.
Table station has the full names of all stations saved. Each station has its unique ID (stationid is primary key).
Table Route has all route information. Each route has its unique ID (routeid is the primary key) and the starting station (fromstationid) and the terminal station (tostationid).
Now there is foreign key relationship between fromstationid, tostationid from the table route with stationid from the table station.
Now I want my website to spell out the following information:
Column 1: Route ID
Column 2: Full name of the starting station
Column 3: Full name of the terminal station
I have formulated two SQL queries.
SELECT route.routeid, route.fromstationid, station.fullname
FROM route INNER JOIN
station
ON route.fromstationid=station.stationid;
SELECT route.routeid, route.tostationid, station.fullname
FROM route INNER JOIN
station
ON route.totationid=station.stationid
Is there any to get this done with just one SQL query (ideally on a SQL database)? Have I missed an important relational database concept?
You're very close. You just need to add the second join into the query, like so:
SELECT r.routeid, r.fromstationid, s1.fullname as start_station, r.tostationid, s2.fullname as end_station
FROM route r
LEFT JOIN station s1 ON r.fromstationid = s1.stationid
LEFT JOIN station s2 ON r.tostationid = s2.stationid
Yes, you just need to join to station twice:
SELECT r.routeid, sfrom.fullname as fromstationname, sto.fullname as tostationname
FROM route r INNER JOIN
station sfrom
ON r.fromstationid = sfrom.stationid INNER JOIN
station sto
ON r.totationid = sto.stationid