SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    A Smarter Way to Web! zivo's Avatar
    Join Date
    Aug 2003
    Location
    Chicago
    Posts
    433
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Multiple joins to the same table

    Hello:

    I have a table named "code" that I use for miscellaneous values in a CMS. For example, on a form where there is a picture, the table would store the values "left", "center" and "right" for the alignment of an image or text. The table has 3 columns - code_type, code_value and code_desc. So for this example, one row would be "align", 1, "left". To get the alignment, I woudl do a join of the content and code tables.

    I have an example where I would like to join multiple values in a content table - a staff table - with the code table. The code_types would be "prefix" and "suffix" (for Dr. and PhD, respectively) in addition to the one I have for the content category. The code I have working with the category join is as follows:

    Code:
    SELECT code_description as category_desc, prefix, first_name, middle_init, last_name, suffix, title, phone, email
    FROM staff, code
    WHERE (code_type = 'STAFFCATEGORY'
    AND category = val(code_value))
    ORDER BY category, priority
    (Note - the Val() is a ColdFusion command)

    Any assistance with how I can enhance this query to get the description values for the prefix and suffix would be greatly appreciated.

    thnx... mp/m

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select cat.code_description as category_desc
         , prf.code_description as prefix
         , first_name
         , middle_init
         , last_name
         , suf.code_description as suffix
         , title
         , phone
         , email
      from staff
    inner 
      join code as cat
        on staff.category = cat.code_value
       and cat.code_type = 'STAFFCATEGORY'
    inner 
      join code as prf
        on staff.prefix = prf.code_value
       and prf.code_type = 'STAFFPREFIX'
    inner 
      join code as suf
        on staff.prefix = suf.code_value
       and suf.code_type = 'STAFFSUFFIX'
    order 
        by cat.code_description 
         , priority
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    A Smarter Way to Web! zivo's Avatar
    Join Date
    Aug 2003
    Location
    Chicago
    Posts
    433
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Thanks!

    Thanks for the help!

    mp/m


Bookmarks

Posting Permissions

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