Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2008
    Posts
    7

    Unanswered: Broken design or simple query issue?

    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.

    Help please?

    Locations
    @key
    Name

    Rates
    @key
    Origin
    Destination
    Rate

    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

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you would need 2 representations of the locations table in the query, otherwise your origin and destination would have to be the same location. Something like:

    Code:
    SELECT L1.name AS Origin
             , L2.nameAS Destination
             , R.ammount AS rate
        FROM Rates AS R
    inner join Locations AS L1
        ON R.Origin = L1.key
    inner join Locations AS L2
        ON R.destination = L2.key

  3. #3
    Join Date
    Jul 2008
    Posts
    7
    Quote Originally Posted by dav1mo View Post
    you would need 2 representations of the locations table in the query, otherwise your origin and destination would have to be the same location. Something like:

    Code:
    SELECT L1.name AS Origin
             , L2.nameAS Destination
             , R.ammount AS rate
        FROM Rates AS R
    inner join Locations AS L1
        ON R.Origin = L1.key
    inner join Locations AS L2
        ON R.destination = L2.key
    There it is!

    Thanks a lot man.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •