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

 

[ssims]

That will give me all of the records I need, but not in the format I'm
trying to get. Allow me to try to simplify.

Surveys - PK_SurveyID, CQ1, CQ2, CQ3, CQ4
Instructors - PK_InstructorID, FK_SurveyID, IQ1, IQ2, IQ3, IQ4

For every Survey records there can be one or two Instructor records.
If I JOIN the Instructors table twice to Surveys I only get those
Survey records with two Instructors. I need a way to join the tables
with a result structure of:

PK_SurveyID, CQ1, CQ2, CQ3, CQ4, PK_InstructorID, IQ1, IQ2, IQ3, IQ4,
PK_InstructorID, IQ1, IQ2, IQ3, IQ4

and results of:

1, 5, 5, 5, 5, 1, 3, 3, 3, 3, 2, 4, 4, 4, 4
2, 5, 5, 5, 5, 3, 2, 2, 2, 2, NULL, NULL, NULL, NULL, NULL
3, 5, 5, 5, 5, 4, 1, 1, 1, 1, 5, 2, 2, 2, 2

I don't know if this is possible through SQL alone, or if I have to do
some complex reading of one table and then reading of another table
with the results from the first query and then reading the whole
shebang into another array or dataadapter or whatever...

-Sean

 

[ssims]

After doing some additional reading on joins and a few hours of trial
and error I came up with a solution to my problem:

SELECT *
FROM Surveys
LEFT OUTER JOIN Instructors I1 ON I1.FK_SurveyID = PK_SurveyID
LEFT OUTER JOIN Instructors I2 ON I1.FK_NameID <> I2.FK_NameID AND
I2.FK_SurveyID = PK_SurveyID
WHERE I1.PK_InstructorID IN (SELECT MIN(PK_InstructorID) FROM
Instructors GROUP BY FK_SurveyID)

-Sean