SQLTeam.com | Weblogs | Forums

Problem with INNER JOIN on more than 2 tables

#1

Hi

I'm new here so sorry if this is already covered somewhere.

Anyway, I'm trying to create a new recordset in Dreamweaver CS6. After a lot of googling and hours of trying, I almost have it working.

There are 3 tables linking into my new table. However 2 of the columns are filled from the same table depending on their role in that table. I'll try to explain a bit more.

I'm creating a database for job interviews. In the interview table there is a InterviewID, InterviewDate, TitleOfPost (titles come from another table), Hospital (Hospital name comes from another table) and then there is a Chairperson and a Secretary. These last 2 come from a 'Boardmembers' table and within that there is a Role field. The 2 options in that are Chairperson and a Secretary.

I'm trying to create the Interview table and I have this so far:

SELECT InterviewID, InterviewDate, Post, HospitalName, Boardmembername AS Chairperson, BoardmemberName AS Secretary FROM Interview_tbl
INNER JOIN TitleOfPost_tbl ON TitleOfPost_tbl.PostID = Interview_tbl.TitleOfPost
INNER JOIN Hospital_tbl ON Hospital_tbl.HospitalID = Interview_tbl.Hospital
INNER JOIN Boardmember_tbl ON Boardmember_tbl.BoardmemberID = Interview_tbl.Chairperson

This all works and displays fine, however I can't get the last bit of information (the name of the secretary) to display. Seems to be a problem because the Chairperson and Secretary details come from the same table.

We are experimenting with code highlighting
#2

Just to confirm ... this is SQL Server?

You can join to the same table twice. Maybe something like:

SELECT InterviewID, InterviewDate, Post, 
    HospitalName, Boardmembername AS Chairperson, 
    SEC.BoardmemberName AS Secretary 
FROM Interview_tbl 
INNER JOIN TitleOfPost_tbl ON TitleOfPost_tbl.PostID = Interview_tbl.TitleOfPost
INNER JOIN Hospital_tbl ON Hospital_tbl.HospitalID = Interview_tbl.Hospital
INNER JOIN Boardmember_tbl ON Boardmember_tbl.BoardmemberID = Interview_tbl.Chairperson
INNER JOIN Boardmember_tbl SEC ON SEC.BoardmemberID = Interview_tbl.Secretary
#3

Thanks for your quick reply. I'm not sure if it's SQL Server? I'm using phpmyadmin and Dreamweaver. Apologies if this the wrong forum.

I tried what you said and I'm getting an error saying that Boardmembername is ambiguous.

#4

Do an alias on all tables, like this:

select i.InterviewID
      ,i.InterviewDate
      ,p.Post
      ,h.HospitalName
      ,b1.Boardmembername as Chairperson
      ,b2.BoardmemberName as Secretary
  from Interview_tbl as i
       inner join TitleOfPost_tbl as p
               on p.PostID=i.TitleOfPost
       inner join Hospital_tbl as h
               on h.HospitalID=i.Hospital
       inner join Boardmember_tbl as b1
               on b1.BoardmemberID=i.Chairperson
       inner join Boardmember_tbl as b2
               on b2.BoardmemberID=i.Secretary
#5

Thank you! That seems to work :smiley:

Now just have to finish the rest of my project by Friday. That was the stumbling block though.

#6

There are as many naming conventions as there are days in a year ... but in case it is food-for-thought I would Alias the two BoardMember tables as B_Chair and B_Sec, rather than B1 and B2. Then when you have

SELECT
      ,B_Chair.Boardmembername as Chairperson
      ,B_Sec.BoardmemberName as Secretary

its clear that you are using the appropriate aliased table. For example if you got them the wrong way round just reading the code is more likely to help you spot an error

SELECT
      ,B_Sec.Boardmembername as Chairperson
      ,B_Chair.BoardmemberName as Secretary

That's the theory anyway!

Here we are also fussy about capitalisation, in your original you have

Boardmembername AS Chairperson, 
BoardmemberName AS Secretary

we would expect programmers to use the correct capitalisation (whichever of those two is correct, probably the one with "Name" given the names of your other columns). The devil is in the detail, but sticking to a naming convention should help spot errors in coding when the naming convention suggests one thing and the written code reads differently.

For example, you have a "_tbl" suffix on your table names. If I typed "FROM FooBar" that would suggest an error as the table name suffix is missing. (Whether to use suffixes for tables is a whole different debate! What happens if you replace a Table with a View? ...)

(But the difference in "BoardmemberName" here might just be typing this for the forum question of course, I'm just rabbiting on in case it is of interest )