ASP.NET / MSSQL 2000 - Join table once or twice.

[ssims]

I've been thinking about this problem for awhile, and can't seem to
come up with a valid answer. I've got two tables, Surveys and
Instructors. When a survey is completed there are one or two
instructors associated with it (no surveys with zero, and no surveys
with more than two). I'd like to take the data from both tables and
join them to display the results. Right now I accomplish displaying
all surveys with two instructors with the following SQL command:

SELECT S.PK_SurveyID, S.Name, S.CQ1, S.CQ2, S.CQ3, S.CQ4,
N1.Name as I1Name, I1.IQ1 as I1IQ1, I1.IQ2 as I1IQ2,
N2.Name as I2Name, I2.IQ1 as I2IQ1, I2.IQ2 as I2IQ2
FROM Surveys S
LEFT JOIN Instructors I1 ON S.PK_SurveyID = I1.FK_SurveyID
LEFT JOIN Names N1 ON I1.FK_NameID = N1.PK_NameID
LEFT JOIN Instructors I2 ON S.PK_SurveyID = I2.FK_SurveyID
LEFT JOIN Names N2 ON I2.FK_NameID = N2.PK_NameID
WHERE
I1.FK_NameID <> I2.FK_NameID AND
I1.PK_InstructorID IN (SELECT MIN(PK_InstructorID) FROM Instructors
GROUP BY FK_SurveyID)
ORDER BY S.PK_SurveyID;

The problem is that if there is only one instructor associated with the
survey it doesn't show up in these results, which is correct per the
query, but I need a way to get all the results and join them
appropriately. Anybody have any ideas? I was thinking of querying the
Surveys table, and then for each surveyID, querying the Instructors
table, but I can't decide how to match those results correctly.

-Sean

 

[Robbe Morris [C# MVP]]

UNION clause

something like this:

select colA as FirstName,
colB as LastName,
instructorID1 as InstructorID
instructorName as InstructorName
from tableA
LEFT Join on tableA.InstructorID1 = Instructors.InstructorID
WHERE stuff
union
select colA as FirstName,
colB as LastName,
instructorID2 as InstructorID,
instructorName as InstructorName
from tableA
LEFT Join on tableA.InstructorID2 = Instructors.InstructorID
WHERE stuff
group by 1,2 -- column orders you want to group by instead of name
order by stuff

you get the idea...

--
Robbe Morris - 2004-2006 Microsoft MVP C#
I've mapped the database to .NET class properties and methods to
implement an multi-layered object oriented environment for your
data access layer. Thus, you should rarely ever have to type the words
SqlCommand, SqlDataAdapter, or SqlConnection again.
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp