Join… without JOINs – The Joys of Temp Tables

Okay, apparently I lose at SQL joins.  I spent a good long while (read: way too long) trying to mix and mash up three database tables to provide a slick LEFT JOIN output (I wanted the nulls to pump into a DataSet for select and updating).

Yeah, that didn’t work out so hot.  I’ll accept the fact that TSQL is still not my strongest skill—I’m past the SELECT * point, but not by a lot.  I much prefer to do the logic in C#… again, which I could have used managed objects if this was hitting a SQL 2005 box and not 2000.  But, I digress.

Scenario:

I had a table of questions.  I had a table of answers by report.  Think of this like a grade card (it was something similar, so I can related it that way).

To begin, I created a simple temporary table.

CREATE TABLE #TempTable 
	(reportid int, 
	questionid int, 
	questiontext varchar(50), 
	sectionid int, 
	displayorder int, 
	answerid int)

Then, we’re ready to insert our questions into the table. The @variables are in place because this information will be coming in from a Stored Procedure.

INSERT #TempTable
SELECT
	@reportid as 'int_reportid',
	q.questionid,
	q.questiontext,
	qs.sectionid,
	q.displayorder,
	' ' as ‘answerid

	FROM 
	Questions q,
	QuestionSections qs

	WHERE 
	q.bool_active = 1 AND
	qs.sectionid = @sectionid AND
	qs.sectionid = q.sectionid

So, how do we populate that answerid column?  An UPDATE. 

UPDATE #TempTable
SET #TempTable.answerid = rr.answerid
FROM #TempTable t, ReportAnswers ra
WHERE 
(t.reportid = ra.reportid) AND
(t.questionid = ra.questionid)

So, that leaves our final steps of selecting (and returning) the TempTable, and then destroying it.

SELECT * FROM #TempTable
DROP TABLE #TempTable

From here, save your stored procedure (or query) and run.

There’s one caveat that I’ve found.  The DataSet objects (XSD’s) in Visual Studio cannot read the columns.  This isn’t a ‘big’ deal if you’re just using these for the queries; however, it’s a hassle I’ve yet to work though.