Left Outer Join across multiple tables question

[okiiyama]

I apologize if my jargon is not correct, anyways:

A realtor sends out a feedback form to showing agents who have shown a certain property of theirs. I need(ed) to count for each property, each question, how many times each possible option for that question was chosen.

At first I had a query that had all but one table in the from clause using the where clause to get the correct results. Then I added my last table using left outer join, beacause there would be some rows with no values in the columns joined. This caused some errors, and I am wondering that is this because I was joining columns from multiple tables that were in the From Clause, and were not joined themselves?

This Works (left outer joining all tables except for fb_questionlist):

select 	f.mlsnum, ql.questionlistid, ql.answertypeid, ag.answergroupid, ql.questionnumber, ql.question, ag.answergroupitem, ag.answergroupitemvalue, isNull(a.answercount, 0) answercount
from 	acfeedback.dbo.fb_questionlist ql
left outer join acfeedback.dbo.fb_answertypes at on at.answertypeid = ql.answertypeid
left outer join acfeedback.dbo.fb_answergroups ag on ag.answergroupid = at.answergroupid
left outer join acfeedback.dbo.fb_forms f on f.questionlistid = ql.questionlistid 
left outer join (
	select f.mlsnum, a.answergroupid, a.questionnumber, a.answertext, count(*) answercount
	from acfeedback.dbo.fb_answers a, acfeedback.dbo.fb_forms f
	where f.formid = a.formid
	group by f.mlsnum, a.answergroupid, a.questionnumber, a.answertext
) a on a.mlsnum = f.mlsnum and a.answergroupid = ag.answergroupid and a.questionnumber = ql.questionnumber and a.answertext = ag.answergroupitem 
group by f.mlsnum, ql.questionlistid, ql.questionnumber, ql.question, ql.answertypeid, ag.answergroupid, ag.answergroupitem, ag.answergroupitemvalue, a.answercount
order by ql.questionnumber, ag.answergroupitemvalue

This Doesn't (only left outer joining one table to the rest):

select 	f.mlsnum, ql.questionlistid, ql.answertypeid, ag.answergroupid, ql.questionnumber, ql.question, ag.answergroupitem, ag.answergroupitemvalue, isNull(a.answercount, 0) answercount
from 	acfeedback.dbo.fb_questionlist ql, acfeedback.dbo.fb_answertypes,
	acfeedback.dbo.fb_answergroups ag, acfeedback.dbo.fb_forms f
left outer join 
	(
		select f.mlsnum, a.answergroupid, a.questionnumber, a.answertext, count(*) answercount
		from acfeedback.dbo.fb_answers a, acfeedback.dbo.fb_forms f
		where f.formid = a.formid
		group by f.mlsnum, a.answergroupid, a.questionnumber, a.answertext
	) a on a.mlsnum = f.mlsnum and a.answergroupid = ag.answergroupid and a.questionnumber = ql.questionnumber and a.answertext = ag.answergroupitem 
where 	at.answertypeid = ql.answertypeid
and 	ag.answergroupid = at.answergroupid
and 	f.questionlistid = ql.questionlistid
group by f.mlsnum, ql.questionlistid, ql.questionnumber, ql.question, ql.answertypeid, ag.answergroupid, ag.answergroupitem, ag.answergroupitemvalue, a.answercount
order by ql.questionnumber, ag.answergroupitemvalue

My errors for the above are:
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'at' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'at' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'ag' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'ql' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'ag' does not match with a table name or alias name used in the query.

I'm really only asking as a matter of discussion.


But thanks in advance.

 

[MattiasJ]

Hi,


One thing is that you have left out the alias "at" in the from clause.

One other thing I would try is to be concistent.
Use ansi style all the why, don't mix it. Use inner join, left join etc.
I think that could solve your problem.

Regards
/Mattias