Results 1 to 10 of 10
  1. #1
    Join Date
    May 2008
    Posts
    97

    Unanswered: Multiple Foreign Keys to Same Table

    Hi guys, I came across a database structure I haven't seen before. And I am completely lost! It would be nice if someone could explain how to do a join on a vocabulary table.

    Note: All FK's are linked to 1 table, the Vocabulary Table (which contains the text representation of the all our IDs in the BikesAttribute table)

    =================
    BikesAtributes

    bikeid (pk, fk)
    atributeid (pk, fk)
    valid (pk, fk)
    UnitMeasurementID (fk)

    =================

    VocabularyTable

    ID (Pk)
    Text

    ==================


    I'm trying to return all the attributes o the 'BikeAttributes' Table - with all its FK's converted to their Text representation.

    Any help would be appreciated.
    Last edited by Eric the Red; 05-18-11 at 12:19.

  2. #2
    Join Date
    May 2008
    Posts
    97
    Is this a case where I need a subquerry in a join? Or would that be complicating thing...

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT *
       FROM bikesattributes AS Attributes
       LEFT JOIN Vocabulary AS Bike
          ON (Bike.ID = Attributes.bikeid)
       LEFT JOIN Vocabulary AS ThisAttribute
          ON (ThisAttribute.ID = Attributes.attributeid)
       LEFT JOIN Vocabulary AS AttribValid
          ON (AttribValid.ID = Attributes.valid)
       LEFT JOIN Vocabulary AS UnitMeasurement
          ON (UnitMeasurement.ID = Attributes.UnitMeasurementID)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    May 2008
    Posts
    97
    Quote Originally Posted by Pat Phelan View Post
    Code:
    SELECT *
       FROM bikesattributes AS Attributes
       LEFT JOIN Vocabulary AS Bike
          ON (Bike.ID = Attributes.bikeid)
       LEFT JOIN Vocabulary AS ThisAttribute
          ON (ThisAttribute.ID = Attributes.attributeid)
       LEFT JOIN Vocabulary AS AttribValid
          ON (AttribValid.ID = Attributes.valid)
       LEFT JOIN Vocabulary AS UnitMeasurement
          ON (UnitMeasurement.ID = Attributes.UnitMeasurementID)
    -PatP
    Thanks but don't I need a reference to the Vocabulary 'Text' field? Maybe I'm not getting it

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The asterisk gets you every column from every alias (table) in the join. You can be more selective in terms of the columns you retrieve and how you use them.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    May 2008
    Posts
    97
    Quote Originally Posted by Pat Phelan View Post
    The asterisk gets you every column from every alias (table) in the join. You can be more selective in terms of the columns you retrieve and how you use them.

    -PatP
    Ahh Great! I didn't think the '*' worked on the joined tables to! Thanks

  7. #7
    Join Date
    May 2008
    Posts
    97
    Quote Originally Posted by Pat Phelan View Post
    Code:
    SELECT *
       FROM bikesattributes AS Attributes
       LEFT JOIN Vocabulary AS Bike
          ON (Bike.ID = Attributes.bikeid)
       LEFT JOIN Vocabulary AS ThisAttribute
          ON (ThisAttribute.ID = Attributes.attributeid)
       LEFT JOIN Vocabulary AS AttribValid
          ON (AttribValid.ID = Attributes.valid)
       LEFT JOIN Vocabulary AS UnitMeasurement
          ON (UnitMeasurement.ID = Attributes.UnitMeasurementID)
    -PatP
    I have to really thank you for this. I am amazed with how I can now access via,

    Bike.text
    ThisAttribute.text
    attributevalid.text
    unitmeasurement.text
    This is the coolest thing I've learned in SQL yet! Thanks so much!!!!!

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Learning about aliases and how they can let you access the same table more than once within a single SQL statement is a big "breakthrough" in using SQL. That makes many things easy that are otherwise impossible.

    One thing you might want to think about is that creative use of aliases allows you to view the current and the "next" row within a single SQL statement, which often will let you eliminate a cursor loop or worse still a client side loop. I have seen this improve code performance by a factor of much more than 100 times.

    As an observation, picking good alias names takes a bit of effort, but it makes reading the SQL so much easier that I always take the time to do it right.. The sanity that I save down the road will probably be my own!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan View Post
    As an observation, picking good alias names takes a bit of effort, but it makes reading the SQL so much easier that I always take the time to do it right.
    i agree with you, properly chosen aliases make a huge difference in query comprehension

    but blindman disagrees, and insists upon using the full table name -- but we always knew he was a bit weird
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937 View Post
    but blindman disagrees, and insists upon using the full table name -- but we always knew he was a bit weird
    Just a bit ???

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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