January 5, 2010 at 4:27 am
i want to remove second left join form below query
-----------------------------------------------------------------------------------------
select r.row_choice,isnull(leftlabel_avg,.001),isnull(rightlabel_avg,.001) ,
@left_label as left_label,
@right_label as right_label
from
#rowtmp r
left join
(select inner_qstn_text, Avg(cast(c.seqn as float)) as leftlabel_avg -----> change
from dbo.respondent_answer_detail rad with (nolock)
inner join #coltmp c
on rad.inner_qstn_choice = c.left_label -----> change
and rad.answ_text = c.col_choice
inner join @tmpRespondent tm
on tm.respondent_stub = rad.respondent_stub
inner join dbo.[RESPONDENT_AUDIT] ra with (nolock)
on ra.acct_id = rad.acct_id
and ra.survey_stub = rad.survey_stub
and ra.respondent_stub = rad.respondent_stub
and ra.respondent_start_date between @startdate and @enddate
where rad.survey_stub=@survey_stub and
rad.qstn_stub= @qstn_stub and
rad.acct_id=@acct_id
group by inner_qstn_text
) ltbl
on r.row_choice=ltbl.inner_qstn_text
left join
(select inner_qstn_text, Avg(cast(c.seqn as float)) as rightlabel_avg -----> change
from dbo.respondent_answer_detail rad with (nolock)
inner join #coltmp c
on rad.inner_qstn_choice = c.right_label -----> change
and rad.answ_text = c.col_choice
inner join @tmpRespondent tm
on tm.respondent_stub = rad.respondent_stub
inner join dbo.[RESPONDENT_AUDIT] ra with (nolock)
on ra.acct_id = rad.acct_id
and ra.survey_stub = rad.survey_stub
and ra.respondent_stub = rad.respondent_stub
and ra.respondent_start_date between @startdate and @enddate
where rad.survey_stub=@survey_stub and
rad.qstn_stub= @qstn_stub and
rad.acct_id=@acct_id
group by inner_qstn_text
) rtbl
on r.row_choice=rtbl.inner_qstn_text
--------------------------------------------------------------------------------
The only difference is (marked in query "---------->") is the on clause condtion woth #tmpcol table
how can i avoid second left join and can have only one left join ( i can fetch both "rightlabel_avg" and "leftlabel_avg" from one left join ???
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 5, 2010 at 4:30 am
Why do you want to remove the second left join? What's wrong with the query as-is?
If you want that rewritten, you're going to have to give us a bit more to work with. Like what it does, the table structures and some sample data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2010 at 4:37 am
my senior DBA suggest that
we should avoid this left join
table schema
------------------------------------------------
declare @tempRetutnTable table
(
inner_qstn_text nvarchar(max),
leftlabel_avg float,
rightlabel_avg float,
left_label nvarchar(200),
right_label nvarchar(200)
)
CREATE TABLE #coltmp
(
seqn int identity(1,1),
col_choice nvarchar(4000),
left_label nvarchar(200),
right_label nvarchar(200)
)
CREATE TABLE #rowtmp
(
seqn int identity(1,1),
row_choice nvarchar(4000),
)
declare @tmpRespondent table( respondent_stub ut_stub)
create table respondent_answer_detail (
respondent_answ_dtl_stub uniqueidentifier
acct_id int
survey_stub uniqueidentifier
respondent_stub uniqueidentifier
qstn_stub uniqueidentifier
inner_qstn_text nvarchar
inner_qstn_choice nvarchar
answ_text nvarchar
answ_tag_id smallint
)
create table [RESPONDENT_AUDIT] (
acct_id int
respondent_audit_id bigint
respondent_stub uniqueidentifier
survey_stub uniqueidentifier
respondent_ip nvarchar
respondent_start_date datetime
respondent_end_date datetime
respondent_last_edit_date datetime
plnr_last_edit_date datetime
plnr_completed_date datetime
respondent_domain nvarchar
respondent_browser nvarchar
http_request_info nvarchar
reference_id nvarchar
created_date datetime
)
----------------------------------------------------------
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 5, 2010 at 5:31 am
Bhuvnesh (1/5/2010)
my senior DBA suggest thatwe should avoid this left join
Then ask him why and how he suggest going about it.
It doesn't make any sense to say 'remove the left join' without a reason for doing so.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2010 at 5:34 am
he explained the same reason i mentioed above that
we are using same tables twice and only differnce is on clause in #tmpcol
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 5, 2010 at 5:49 am
Are you returning different rows from the tables in each part? If so, it may be the easiest way of doing this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2010 at 5:51 am
yes both left join will give me two different results
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 5, 2010 at 6:00 am
You may be able to remove both subqueries and just have one query with an OR in the join clause. (INNER JOIN <second table> ON <condition> OR <condition>). It'll look better, no promises that it'll run better though
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2010 at 6:02 am
I am with Gail on this one. Without more to go on, (sample data, table structures, etc.) I think that query does what you want and I don't think you will be saving anything by trying to rewrite it without the second left join - the criteria is different for each result set and that is why the second query makes sense. Again just a guess without more information.
January 5, 2010 at 8:04 am
Does this work?
select r.row_choice,isnull(leftlabel_avg,.001),isnull(rightlabel_avg,.001) ,
@left_label as left_label,
@right_label as right_label
from
#rowtmp r
left join
(select inner_qstn_text,
case
when rad.inner_qstn_choice = c.left_label
then Avg(cast(c.seqn as float)) else null end as leftlabel_avg,
case
when rad.inner_qstn_choice = c.right_label
then Avg(cast(c.seqn as float)) else null end as rightlabel_avg
from dbo.respondent_answer_detail rad with (nolock)
inner join #coltmp c
on rad.answ_text = c.col_choice
inner join @tmpRespondent tm
on tm.respondent_stub = rad.respondent_stub
inner join dbo.[RESPONDENT_AUDIT] ra with (nolock)
on ra.acct_id = rad.acct_id
and ra.survey_stub = rad.survey_stub
and ra.respondent_stub = rad.respondent_stub
and ra.respondent_start_date between @startdate and @enddate
where rad.survey_stub=@survey_stub and
rad.qstn_stub= @qstn_stub and
rad.acct_id=@acct_id
group by inner_qstn_text
) tbl
on r.row_choice=tbl.inner_qstn_text
January 5, 2010 at 10:27 pm
the inner derived table ( pasted below ) gives an error
-Query : ------------------------
select
rad.inner_qstn_text,case when rad.inner_qstn_choice = c.left_label then Avg(cast(c.seqn as float)) else null end as leftlabel_avg,
case when rad.inner_qstn_choice = c.right_label then Avg(cast(c.seqn as float)) else null end as rightlabel_avg
from dbo.respondent_answer_detail rad with (nolock)
inner join #coltmp c
on rad.answ_text = c.col_choice
inner join @tmpRespondent tm
on tm.respondent_stub = rad.respondent_stub
inner join dbo.[RESPONDENT_AUDIT] ra with (nolock)
on ra.acct_id = rad.acct_id
and ra.survey_stub = rad.survey_stub
and ra.respondent_stub = rad.respondent_stub
and ra.respondent_start_date between @startdate and @enddate
where rad.survey_stub=@survey_stub and
rad.qstn_stub= @qstn_stub and
rad.acct_id=@acct_id
group by rad.inner_qstn_text
-error : ------------------------------
Msg 8120, Level 16, State 1, Procedure up_rsrpt_Survey_SideBySideMatrixResponseByCategory1, Line 185
Column 'dbo.respondent_answer_detail.inner_qstn_choice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure up_rsrpt_Survey_SideBySideMatrixResponseByCategory1, Line 185
Column '#coltmp.left_label' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 5, 2010 at 10:47 pm
If you use aggregate functions on columns in the Select list, then every column that is not being aggregated needs to be in the Group by clause.
Try this
select
rad.inner_qstn_text,case when rad.inner_qstn_choice = c.left_label then Avg(cast(c.seqn as float)) else null end as leftlabel_avg,
case when rad.inner_qstn_choice = c.right_label then Avg(cast(c.seqn as float)) else null end as rightlabel_avg
from dbo.respondent_answer_detail rad with (nolock)
inner join #coltmp c
on rad.answ_text = c.col_choice
inner join @tmpRespondent tm
on tm.respondent_stub = rad.respondent_stub
inner join dbo.[RESPONDENT_AUDIT] ra with (nolock)
on ra.acct_id = rad.acct_id
and ra.survey_stub = rad.survey_stub
and ra.respondent_stub = rad.respondent_stub
and ra.respondent_start_date between @startdate and @enddate
where rad.survey_stub=@survey_stub and
rad.qstn_stub= @qstn_stub and
rad.acct_id=@acct_id
group by rad.inner_qstn_text,
rad.inner_qstn_choice, c.left_label
January 6, 2010 at 3:24 am
Sorry I didnot notice the group by clause. Here's the correction
select r.row_choice,isnull(leftlabel_avg,.001),isnull(rightlabel_avg,.001) ,
@left_label as left_label,
@right_label as right_label
from
#rowtmp r
left join
(select inner_qstn_text,
Avg(case when rad.inner_qstn_choice = c.left_label then cast(c.seqn as float) else null end) as leftlabel_avg,
Avg(case when rad.inner_qstn_choice = c.right_label then cast(c.seqn as float) else null end) as rightlabel_avg
from dbo.respondent_answer_detail rad with (nolock)
inner join #coltmp c
on rad.answ_text = c.col_choice
inner join @tmpRespondent tm
on tm.respondent_stub = rad.respondent_stub
inner join dbo.[RESPONDENT_AUDIT] ra with (nolock)
on ra.acct_id = rad.acct_id
and ra.survey_stub = rad.survey_stub
and ra.respondent_stub = rad.respondent_stub
and ra.respondent_start_date between @startdate and @enddate
where rad.survey_stub=@survey_stub and
rad.qstn_stub= @qstn_stub and
rad.acct_id=@acct_id
group by inner_qstn_text
) tbl
on r.row_choice=tbl.inner_qstn_text
January 6, 2010 at 5:56 am
thanks a lot
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply