Hello guys, I need help with a query of a Database I designed.
The Query I want to generate uses two tables, one for locations used to store the names of locations a company of transportation transports cargo, and one for rates, which is got three columns asides from the primary key, Origin, Destination and the rate.
The origin and destination are foreign keys linked to the primary key of the locations table, and this is where things get fuzzy since I know that's a repeated field violating 1st normal form, but since I need to calculate rates from one location to another and vice versa, I'm kind of confused as to how design the table.
Here's the query I have so far. (hint: Doesn't work :P)
SELECT L.name AS Origin, L.nameAS Destination, R.ammount AS rate
FROM Locations AS L INNER JOIN
Rates AS R ON R.Origin = L.key and R.destination = L.key