SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Joining same table twice

    I have a ColdFusion page that lists web pages, titles, and contact names. It's pretty simple, and uses this query (MS Access):

    Code SQL:
    SELECT pagecontacts.page_id
    	 , pagecontacts.page_name
    	 , pagecontacts.page_title
    	 , pagecontacts.page_office
    	 , pagecontacts.contact_id
    	 , maindir.main_id
    	 , maindir.first_name & ' ' & maindir.last_name AS fullname
    	 , maindir.email
    FROM (
    	pagecontacts
    LEFT
    	JOIN maindir
    	  ON pagecontacts.contact_id = maindir.main_id
    	  )
    ORDER BY page_id

    In the database I have a table with the following columns:

    page_id (primary, autonumber)
    page_name
    page_title
    page_office (used to pare down the number of pertinent contacts)
    contact_id (this is a number that corresponds to another db table where things like name, email addy, etc are kept)

    Each web page queries this database to fetch the contact info for that page.

    I have a sort of admin page with an HTML table that lists all of our web pages, along with the contact of the person responsible for the content.

    Some of our pages need a second contact person, so I've added a new column, alt_contact_id, to the table. Now I want to add the alt contact person to the HTML table so that I can display both on the admin page.

    I was heading in this direction:

    Code SQL:
    SELECT pagecontacts.page_id
    	 , pagecontacts.page_name
    	 , pagecontacts.page_title
    	 , pagecontacts.page_office
    	 , pagecontacts.contact_id
    	 , pagecontacts.alt_contact_id
    	 , maindir.main_id
    	 , maindir.first_name & ' ' & maindir.last_name AS fullname
    	 , maindir.email
    FROM ( (
    	pagecontacts
    LEFT
    	JOIN maindir AS a
    	  ON pagecontacts.contact_id = a.main_id
    	  )
    LEFT
    	JOIN maindir AS b
    	  ON pagecontacts.alt_contact_id = b.main_id
    	  )
    ORDER BY page_id

    But that doesn't seem to work. Any advice?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It's ambiguous to which table you're referring in the SELECT portion of the query when you join the same table twice. Use the alias you created so it knows which you want.

  3. #3
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Doh! Of course.

    Code SQL:
    SELECT pagecontacts.page_id
    	 , pagecontacts.page_name
    	 , pagecontacts.page_title
    	 , pagecontacts.page_office
    	 , pagecontacts.contact_id
    	 , pagecontacts.alt_contact_id
    	 , a.main_id
    	 , a.first_name & ' ' & a.last_name AS afullname
    	 , a.email
    	 , b.main_id
    	 , b.first_name & ' ' & b.last_name AS bfullname
    	 , b.email
    FROM ( (
    	pagecontacts
    LEFT
    	JOIN maindir AS a
    	  ON pagecontacts.contact_id = a.main_id
    	  )
    LEFT
    	JOIN maindir AS b
    	  ON pagecontacts.alt_contact_id = b.main_id
    	  )
    ORDER BY page_id

    Works like a charm! Thanks!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    expletive!!

    dan, can you ask to have the "highlight=sql" code interpreter changed so that the colour of the parentheses is something a bit more legible than that horrid pale puke green?

    i've hated it since forever (one of the reasons i stick to using simple [code][/code] blocks)

    don't know why i chose this thread to voice my displeasure
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    There's a forum upgrade coming in the not-too-distant future, maybe that'll come with a newer syntax highlighting plugin.

  6. #6
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Looking at that again, I should probably rename the "email" column to something less reserved-looking. In fact, I'm surprised I named it that way in the first place.


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
  •