Joining same table twice

Databases
1

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

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:

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

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

Doh! Of course.


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

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

5

There’s a forum upgrade coming in the not-too-distant future, maybe that’ll come with a newer syntax highlighting plugin.

6

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.