I have two tables one table model and other table feedback. table model contains all the models but feedback table contains feedback given to only few models. I want list of all the models and feedback of those models who are present in table feedback.
But the problem comes when i need only that feedback which has been approved by the admin. i have a field in the table feedback 'isapproved' (bit)
My query is :
select mod.userid,isnull(SUM(feed.Q1+feed.Q2+feed.Q3+feed.Q4+feed.Q5)/count(feed.modelid)*4,0) as feedback
from tbl_model mod left outer join tbl_feedback feed on feed.modelid=mod.userid
where feed.isapproved=1 group by mod.userid
but this returns only those records with feedback approved, not all the models from table model. I want list of models even if the model who is present in the table model but does not have any feedback in feedback table.
for E.G.
Modelname feedback
abc 0
def 25%
ghi 50%
jkl 0
where abc and jkl are present in the table model but not in the table feedback.
select mod.userid,isnull(SUM(feed.Q1+feed.Q2+feed.Q3+feed.Q4+feed.Q5)/count(feed.modelid)*4,0) as feedback
from tbl_model mod left outer join tbl_feedback feed on feed.modelid=mod.userid where feed.isapproved=1 group by mod.userid
but this returns only those records with feedback approved, not all the models from table model.
I want list of models even if the model who is present in the table model but does not have any feedback in feedback table.
These two things seem a bit contraditory to me, can you remove the where clause, or if you are particular that you want to consider only those feedbacks that are approved then you can rephrase your query as
SELECT MOD.USERID, (SELECT ISNULL(SUM(FEED.Q1 + FEED.Q2 + FEED.Q3 + FEED.Q4 + FEED.Q5)/COUNT(FEED.MODELID)*4,0) FROM TBL_FEEDBACK FEED WHERE FEED.MODELID = MOD.USERID AND FEED.ISAPPROVED=1) FROM TBL_MODEL MOD GROUP BY MOD.USERID
Thanks for your reply. The problem which i am facing is i have to incorportate your solution in my stored proc. so i was looking for a query with subquery kind of solution. i thought of using cursors but then it was getting difficult
to add in my proc. here is my proc. This is a search procedure. :
-- Add the parameters for the stored procedure here
@categories nvarchar(max)=null,
@firstname nvarchar(500)=null,
@lastname nvarchar(500)=null,
@agefrom int = 0,
@ageto int = 0,
@gender nvarchar(10)=null,
@eyecolor nvarchar(20)=null,
@haircolor nvarchar(20)=null,
@locationzip nvarchar(10)=null,
@state nvarchar(50)=null,
@mostvoted bit = null,
@highestfeedback bit = null,
@browselatest bit = null,
@mostviewed bit = null,
@workforfree nvarchar(10) = null,
@photographerid uniqueidentifier='00000000-0000-0000-0000-000000000000',
@agencyid uniqueidentifier='00000000-0000-0000-0000-000000000000'
set @selectqry = 'select mod.userid,mod.isactive,mod.picture1,mod.NoOfViews,mod.modelfname,mod.modellname,mod.submissiondate,mod.biostats,isnull(mod.state,''Not Available'') as State,count(vote.modelid) as votes,isnull(SUM(feed.Q1+feed.Q2+feed.Q3+feed.Q4+feed.Q5)/count(feed.modelid)*4,0)
as feedback from tbl_model mod left join tbl_vote vote on mod.userid = vote.Modelid left join tbl_feedback feed on feed.modelid=mod.userid'
set @groupbycondition = 'group by mod.userid,mod.isactive,mod.NoOfViews,mod.picture1,mod.modelfname,mod.modellname,mod.submissiondate,mod.biostats,mod.state,feed.modelid'
set @whereclause = ' '
set @orderbyclause =' '
/*None is selected*/
if (@categories is null and @firstname is null and @lastname is null and @agefrom = 0 and @ageto = 0 and @gender is null and @eyecolor is null and @haircolor is null and @locationzip is null and @state is null and @mostvoted is null and @highestfeedback is
null and @browselatest is null and @mostviewed is null and @photographerid ='00000000-0000-0000-0000-000000000000' and @agencyid = '00000000-0000-0000-0000-000000000000' and @workforfree is null)
Begin
set @whereclause = ' '
set @orderbyclause =' '
End
Else
BEGIN
/*First Name*/
if (@firstname is not null)
Begin
set @whereclause = ' where mod.modelfname like '''+@firstname+'%'''
End
/*Last Name*/
if (@lastname is not null)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.modellname like '''+@lastname+'%'''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.modellname like '''+@lastname+'%'''
END
End
/*Age*/
if (@agefrom !=0 or @ageto != 0)
BEGIN
/*If only the starting age is given*/
if(@agefrom != 0 and @ageto = 0)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.DOB < dateadd(yy,'+convert(nvarchar(10),-@agefrom)+',getdate())'
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.DOB < dateadd(yy,'+convert(nvarchar(10),-@agefrom)+',getdate())'
END
End
/*If only the End age is given*/
if(@agefrom = 0 and @ageto != 0)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.DOB > dateadd(yy,'+convert(nvarchar(10),-@ageto)+',getdate())'
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.DOB > dateadd(yy,'+convert(nvarchar(10),-@ageto)+',getdate())'
END
End
/*If both starting age and end age are given*/
if(@agefrom != 0 and @ageto != 0)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.DOB < dateadd(yy,'+convert(nvarchar(10),-@agefrom)+',getdate()) and mod.DOB > dateadd(yy,'+convert(nvarchar(10),-@ageto)+',getdate())'
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.DOB < dateadd(yy,'+convert(nvarchar(10),-@agefrom)+',getdate()) and mod.DOB > dateadd(yy,'+convert(nvarchar(10),-@ageto)+',getdate())'
END
END
End
/*Gender*/
if(@gender is not null)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.gender ='''+@gender+''''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.gender ='''+@gender+''''
END
END
/*Eye color*/
if(@eyecolor is not null)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.EyeColor ='''+@eyecolor+''''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.EyeColor ='''+@eyecolor+''''
END
END
/*Hair color*/
if(@haircolor is not null)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.HairColor ='''+@haircolor+''''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.HairColor ='''+@haircolor+''''
END
END
/*Location based on post code*/
if(@locationzip is not null)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.Zip like''%'+@locationzip+'%'''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.Zip like''%'+@locationzip+'%'''
END
END
/*Location based on state*/
if(@state is not null)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.state like''%'+@state+'%'''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.state like''%'+@state+'%'''
END
END
/*Categories*/
if(@categories is not null)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.userid in (select distinct model_id from tbl_Modelavailablefor where model_availablefor in('+@categories+'))'
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.userid in (select distinct model_id from tbl_Modelavailablefor where model_availablefor in('+@categories+'))'
END
END
/*Most Voted*/
if(@mostvoted is not null)
BEGIN
set @orderbyclause = ' order by Votes desc'
END
/*Highest Feedback*/
if(@highestfeedback is not null)
BEGIN
if(@orderbyclause = ' ')
BEGIN
set @orderbyclause = ' order by feedback desc'
End
else
BEGIN
set @orderbyclause = @orderbyclause + ' ,feedback desc'
End
END
/*Browse Latest*/
if(@browselatest is not null)
BEGIN
if(@orderbyclause = ' ')
BEGIN
set @orderbyclause = ' order by mod.submissiondate desc'
End
else
BEGIN
set @orderbyclause = @orderbyclause + ' ,mod.submissiondate desc'
End
END
/*Most Viewed*/
if(@mostviewed is not null)
BEGIN
if(@orderbyclause = ' ')
BEGIN
set @orderbyclause = ' order by mod.NoOfViews desc'
End
else
BEGIN
set @orderbyclause = @orderbyclause + ' ,mod.NoOfViews desc'
End
END
/*Work For Free*/
if(@workforfree is not null)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.workforfree = '''+CONVERT(nvarchar(10),@workforfree)+''''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.workforfree = '''+CONVERT(nvarchar(10),@workforfree)+''''
END
END
/*Photographer Name*/
if (@photographerid != '00000000-0000-0000-0000-000000000000')
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.photographerid ='''+CONVERT(nvarchar(50),@photographerid)+''''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.photographerid ='''+CONVERT(nvarchar(50),@photographerid)+''''
End
END
/*Agency Name*/
if (@agencyid != '00000000-0000-0000-0000-000000000000')
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.agencyid ='''+CONVERT(nvarchar(50),@agencyid)+''''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.agencyid ='''+CONVERT(nvarchar(50),@agencyid)+''''
End
END
END
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.isactive = 1'
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.isactive = 1'
End
exec (@selectqry+@whereclause+@groupbycondition+@orderbyclause)
Thanks buddy, i just incorporated that solution in my query. This is how i have done it :
set @query = ';with cte as(Select mod.userid ,isnull( SUM(feed.Q1+feed.Q2+feed.Q3+feed.Q4+feed.Q5)/count(feed.modelid)*4,0) as feedback from tbl_model [mod] left join tbl_feedback feed on [mod].userid=feed.modelid where feed.isapproved=1 group by mod.userid
)'
set @selectqry = 'select mod.userid,mod.isactive,mod.picture1,mod.NoOfViews,mod.modelfname,mod.modellname,mod.submissiondate,mod.biostats,isnull(mod.state,''Not Available'') as State,count(vote.modelid) as votes,isnull(cte.feedback ,0) as feedback from
tbl_model mod left join cte on mod.userid=cte.userid left join tbl_vote vote on mod.userid = vote.Modelid left join tbl_feedback feed on feed.modelid=mod.userid'
set @groupbycondition = 'group by mod.userid,mod.isactive,mod.NoOfViews,mod.picture1,mod.modelfname,mod.modellname,mod.submissiondate,mod.biostats,mod.state,feed.modelid,cte.feedback'
set @whereclause = ' '
set @orderbyclause =' '
That's actually very common mistake and I was making it repeatedly until I finally learned a simple rule. If you use a LEFT JOIN condition, the restrictions on the right side table MUST be in the AND clause, not in the WHERE. Otherwise placing them in WHERE
clause we "kill" the LEFT JOIN and it becomes an INNER JOIN.
Beware of bugs in the above code; I have only proved it correct, not tried it. (Donald Knuth)
I haven't read this thread thoroughly, but I think you made your SP much more complex than needed. Your first query had a simple error (your condition had to be in the JOIN condition, not in the WHERE). Post your whole problem again with the tables and may
be there is a simple way to solve it without dynamic SQL.
Beware of bugs in the above code; I have only proved it correct, not tried it. (Donald Knuth)
Hi Naom, Thanks for pointing out where my query can be wrong. I have been in .net for almost a year now but sql has been my weak point. This query has not been tested or helped by any senior so i know it is bound to have bugs or there might be performance
issues. Following are the tables that are involved
Following are the search options: Categories, First name, Last name, Age ( @from - @to ) , Gender, Eye color, Hair Color, Work For Free(bit), Post Code, State, Photographerid, agencyid. Then there are sorting options : Most Voted, Highest Feedback, Browse
Latest, Most Viewed.
I have already posted the Stored Proc. I will be glad to hear suggestions and help from the experts
ishan_bakshi
Member
182 Points
158 Posts
Problem in sub query
Dec 16, 2008 01:30 PM|LINK
Hi,
I have two tables one table model and other table feedback. table model contains all the models but feedback table contains feedback given to only few models. I want list of all the models and feedback of those models who are present in table feedback. But the problem comes when i need only that feedback which has been approved by the admin. i have a field in the table feedback 'isapproved' (bit)
My query is :
select mod.userid,isnull(SUM(feed.Q1+feed.Q2+feed.Q3+feed.Q4+feed.Q5)/count(feed.modelid)*4,0) as feedback
from tbl_model mod left outer join tbl_feedback feed on feed.modelid=mod.userid
where feed.isapproved=1 group by mod.userid
but this returns only those records with feedback approved, not all the models from table model. I want list of models even if the model who is present in the table model but does not have any feedback in feedback table.
for E.G.
Modelname feedback
abc 0
def 25%
ghi 50%
jkl 0
where abc and jkl are present in the table model but not in the table feedback.
Kindly guide
skvignesh
Contributor
2670 Points
512 Posts
Re: Problem in sub query
Dec 16, 2008 02:22 PM|LINK
select mod.userid,isnull(SUM(feed.Q1+feed.Q2+feed.Q3+feed.Q4+feed.Q5)/count(feed.modelid)*4,0) as feedback
from tbl_model mod left outer join tbl_feedback feed on feed.modelid=mod.userid
where feed.isapproved=1 group by mod.userid
but this returns only those records with feedback approved, not all the models from table model. I want list of models even if the model who is present in the table model but does not have any feedback in feedback table.
These two things seem a bit contraditory to me, can you remove the where clause, or if you are particular that you want to consider only those feedbacks that are approved then you can rephrase your query as
SELECT MOD.USERID, (SELECT ISNULL(SUM(FEED.Q1 + FEED.Q2 + FEED.Q3 + FEED.Q4 + FEED.Q5)/COUNT(FEED.MODELID)*4,0) FROM TBL_FEEDBACK FEED WHERE FEED.MODELID = MOD.USERID AND FEED.ISAPPROVED=1) FROM TBL_MODEL MOD GROUP BY MOD.USERID
S.K.Vignesh
raghav_khung...
All-Star
32835 Points
5563 Posts
MVP
Re: Problem in sub query
Dec 16, 2008 02:32 PM|LINK
Hi,ishan
Try This Example Related Ur Query
I have made the select query in bold and I have taken Modelid inplace of ur user id
So consider that
CReate
table tbl_model(
modelid int ,modelname varchar(20))Go
insert
into tbl_modelselect
1,'abc'union all
select
2,'def'union all
select
3,'ghi'union all
select 4,'jkl'Go
CReate
table tbl_feedback(
feedbackid int ,modelid int,Q1 int,Q2 int,Q3 int,Q4 int,Q5 int,isapproved bit)Go
insert
into tbl_feedbackselect
1,1,10,10,10,10,10,1union all
select
2,2,10,10,10,10,10,1union all
select 3,3,10,10,10,10,10,0Go
;
with cte as(
Select
mod.modelid ,isnull( SUM(feed.Q1+feed.Q2+feed.Q3+feed.Q4+feed.Q5)/count(feed.modelid)*4,0) as feedbackfrom
tbl_model [mod]left
join tbl_feedback feedon
[mod].modelid=feed.modelid wherefeed
.isapproved=1group
by mod.modelid)
select
tbl_model.[modelname],isnull(cte.feedback ,0) as feedbackfrom
tbl_modelleft
join cte on tbl_model.modelid=cte.modelid--Output
modelname feedback
-------------------- -----------
abc 200
def 200
ghi 0
jkl 0
ishan_bakshi
Member
182 Points
158 Posts
Re: Problem in sub query
Dec 17, 2008 04:11 AM|LINK
HI raghav,
Thanks for your reply. The problem which i am facing is i have to incorportate your solution in my stored proc. so i was looking for a query with subquery kind of solution. i thought of using cursors but then it was getting difficult to add in my proc. here is my proc. This is a search procedure. :
-- Add the parameters for the stored procedure here
@categories nvarchar(max)=null,
@firstname nvarchar(500)=null,
@lastname nvarchar(500)=null,
@agefrom int = 0,
@ageto int = 0,
@gender nvarchar(10)=null,
@eyecolor nvarchar(20)=null,
@haircolor nvarchar(20)=null,
@locationzip nvarchar(10)=null,
@state nvarchar(50)=null,
@mostvoted bit = null,
@highestfeedback bit = null,
@browselatest bit = null,
@mostviewed bit = null,
@workforfree nvarchar(10) = null,
@photographerid uniqueidentifier='00000000-0000-0000-0000-000000000000',
@agencyid uniqueidentifier='00000000-0000-0000-0000-000000000000'
AS
declare @selectqry nvarchar(max)
declare @whereclause nvarchar(max)
declare @groupbycondition nvarchar(max)
declare @orderbyclause nvarchar(max)
set @selectqry = 'select mod.userid,mod.isactive,mod.picture1,mod.NoOfViews,mod.modelfname,mod.modellname,mod.submissiondate,mod.biostats,isnull(mod.state,''Not Available'') as State,count(vote.modelid) as votes,isnull(SUM(feed.Q1+feed.Q2+feed.Q3+feed.Q4+feed.Q5)/count(feed.modelid)*4,0) as feedback from tbl_model mod left join tbl_vote vote on mod.userid = vote.Modelid left join tbl_feedback feed on feed.modelid=mod.userid'
set @groupbycondition = 'group by mod.userid,mod.isactive,mod.NoOfViews,mod.picture1,mod.modelfname,mod.modellname,mod.submissiondate,mod.biostats,mod.state,feed.modelid'
set @whereclause = ' '
set @orderbyclause =' '
/*None is selected*/
if (@categories is null and @firstname is null and @lastname is null and @agefrom = 0 and @ageto = 0 and @gender is null and @eyecolor is null and @haircolor is null and @locationzip is null and @state is null and @mostvoted is null and @highestfeedback is null and @browselatest is null and @mostviewed is null and @photographerid ='00000000-0000-0000-0000-000000000000' and @agencyid = '00000000-0000-0000-0000-000000000000' and @workforfree is null)
Begin
set @whereclause = ' '
set @orderbyclause =' '
End
Else
BEGIN
/*First Name*/
if (@firstname is not null)
Begin
set @whereclause = ' where mod.modelfname like '''+@firstname+'%'''
End
/*Last Name*/
if (@lastname is not null)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.modellname like '''+@lastname+'%'''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.modellname like '''+@lastname+'%'''
END
End
/*Age*/
if (@agefrom !=0 or @ageto != 0)
BEGIN
/*If only the starting age is given*/
if(@agefrom != 0 and @ageto = 0)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.DOB < dateadd(yy,'+convert(nvarchar(10),-@agefrom)+',getdate())'
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.DOB < dateadd(yy,'+convert(nvarchar(10),-@agefrom)+',getdate())'
END
End
/*If only the End age is given*/
if(@agefrom = 0 and @ageto != 0)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.DOB > dateadd(yy,'+convert(nvarchar(10),-@ageto)+',getdate())'
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.DOB > dateadd(yy,'+convert(nvarchar(10),-@ageto)+',getdate())'
END
End
/*If both starting age and end age are given*/
if(@agefrom != 0 and @ageto != 0)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.DOB < dateadd(yy,'+convert(nvarchar(10),-@agefrom)+',getdate()) and mod.DOB > dateadd(yy,'+convert(nvarchar(10),-@ageto)+',getdate())'
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.DOB < dateadd(yy,'+convert(nvarchar(10),-@agefrom)+',getdate()) and mod.DOB > dateadd(yy,'+convert(nvarchar(10),-@ageto)+',getdate())'
END
END
End
/*Gender*/
if(@gender is not null)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.gender ='''+@gender+''''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.gender ='''+@gender+''''
END
END
/*Eye color*/
if(@eyecolor is not null)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.EyeColor ='''+@eyecolor+''''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.EyeColor ='''+@eyecolor+''''
END
END
/*Hair color*/
if(@haircolor is not null)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.HairColor ='''+@haircolor+''''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.HairColor ='''+@haircolor+''''
END
END
/*Location based on post code*/
if(@locationzip is not null)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.Zip like''%'+@locationzip+'%'''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.Zip like''%'+@locationzip+'%'''
END
END
/*Location based on state*/
if(@state is not null)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.state like''%'+@state+'%'''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.state like''%'+@state+'%'''
END
END
/*Categories*/
if(@categories is not null)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.userid in (select distinct model_id from tbl_Modelavailablefor where model_availablefor in('+@categories+'))'
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.userid in (select distinct model_id from tbl_Modelavailablefor where model_availablefor in('+@categories+'))'
END
END
/*Most Voted*/
if(@mostvoted is not null)
BEGIN
set @orderbyclause = ' order by Votes desc'
END
/*Highest Feedback*/
if(@highestfeedback is not null)
BEGIN
if(@orderbyclause = ' ')
BEGIN
set @orderbyclause = ' order by feedback desc'
End
else
BEGIN
set @orderbyclause = @orderbyclause + ' ,feedback desc'
End
END
/*Browse Latest*/
if(@browselatest is not null)
BEGIN
if(@orderbyclause = ' ')
BEGIN
set @orderbyclause = ' order by mod.submissiondate desc'
End
else
BEGIN
set @orderbyclause = @orderbyclause + ' ,mod.submissiondate desc'
End
END
/*Most Viewed*/
if(@mostviewed is not null)
BEGIN
if(@orderbyclause = ' ')
BEGIN
set @orderbyclause = ' order by mod.NoOfViews desc'
End
else
BEGIN
set @orderbyclause = @orderbyclause + ' ,mod.NoOfViews desc'
End
END
/*Work For Free*/
if(@workforfree is not null)
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.workforfree = '''+CONVERT(nvarchar(10),@workforfree)+''''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.workforfree = '''+CONVERT(nvarchar(10),@workforfree)+''''
END
END
/*Photographer Name*/
if (@photographerid != '00000000-0000-0000-0000-000000000000')
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.photographerid ='''+CONVERT(nvarchar(50),@photographerid)+''''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.photographerid ='''+CONVERT(nvarchar(50),@photographerid)+''''
End
END
/*Agency Name*/
if (@agencyid != '00000000-0000-0000-0000-000000000000')
BEGIN
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.agencyid ='''+CONVERT(nvarchar(50),@agencyid)+''''
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.agencyid ='''+CONVERT(nvarchar(50),@agencyid)+''''
End
END
END
if(@whereclause = ' ')
BEGIN
set @whereclause = ' where mod.isactive = 1'
End
else
BEGIN
set @whereclause = @whereclause + ' and mod.isactive = 1'
End
exec (@selectqry+@whereclause+@groupbycondition+@orderbyclause)
raghav_khung...
All-Star
32835 Points
5563 Posts
MVP
Re: Problem in sub query
Dec 17, 2008 04:20 AM|LINK
Hi,ishan
Please Post The Table Structure Of that tables ie Create Table Statement of that tables
and also some Test iNput data in that Tables And What output u required show some test output also
so that I can Help u.
Thanks.
ishan_bakshi
Member
182 Points
158 Posts
Re: Problem in sub query
Dec 17, 2008 04:38 AM|LINK
Hi raghav,
Thanks buddy, i just incorporated that solution in my query. This is how i have done it :
set @query = ';with cte as(Select mod.userid ,isnull( SUM(feed.Q1+feed.Q2+feed.Q3+feed.Q4+feed.Q5)/count(feed.modelid)*4,0) as feedback from tbl_model [mod] left join tbl_feedback feed on [mod].userid=feed.modelid where feed.isapproved=1 group by mod.userid )'
set @selectqry = 'select mod.userid,mod.isactive,mod.picture1,mod.NoOfViews,mod.modelfname,mod.modellname,mod.submissiondate,mod.biostats,isnull(mod.state,''Not Available'') as State,count(vote.modelid) as votes,isnull(cte.feedback ,0) as feedback from tbl_model mod left join cte on mod.userid=cte.userid left join tbl_vote vote on mod.userid = vote.Modelid left join tbl_feedback feed on feed.modelid=mod.userid'
set @groupbycondition = 'group by mod.userid,mod.isactive,mod.NoOfViews,mod.picture1,mod.modelfname,mod.modellname,mod.submissiondate,mod.biostats,mod.state,feed.modelid,cte.feedback'
set @whereclause = ' '
set @orderbyclause =' '
exec (@query+@selectqry+@whereclause+@groupbycondition)
Thanks for your quick reply :) .
raghav_khung...
All-Star
32835 Points
5563 Posts
MVP
Re: Problem in sub query
Dec 17, 2008 04:47 AM|LINK
Hi,Ishan
Glad To Help u. [<:o)]
Naom
All-Star
36004 Points
7901 Posts
Re: Problem in sub query
Dec 17, 2008 04:48 AM|LINK
That's actually very common mistake and I was making it repeatedly until I finally learned a simple rule. If you use a LEFT JOIN condition, the restrictions on the right side table MUST be in the AND clause, not in the WHERE. Otherwise placing them in WHERE clause we "kill" the LEFT JOIN and it becomes an INNER JOIN.
(Donald Knuth)
Visit my blog
Microsoft Community Contributor 2011-12
Naom
All-Star
36004 Points
7901 Posts
Re: Problem in sub query
Dec 17, 2008 04:52 AM|LINK
I haven't read this thread thoroughly, but I think you made your SP much more complex than needed. Your first query had a simple error (your condition had to be in the JOIN condition, not in the WHERE). Post your whole problem again with the tables and may be there is a simple way to solve it without dynamic SQL.
(Donald Knuth)
Visit my blog
Microsoft Community Contributor 2011-12
ishan_bakshi
Member
182 Points
158 Posts
Re: Problem in sub query
Dec 17, 2008 05:30 AM|LINK
Hi Naom, Thanks for pointing out where my query can be wrong. I have been in .net for almost a year now but sql has been my weak point. This query has not been tested or helped by any senior so i know it is bound to have bugs or there might be performance issues. Following are the tables that are involved
CREATE TABLE [dbo].[tbl_Model](
[Modelid] [int] IDENTITY(1,1) NOT NULL,
[Userid] [uniqueidentifier] NULL,
[ModelFname] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ModelLname] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Gender] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DOB] [datetime] NULL,
[EyeColor] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HairColor] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BioStats] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Agencyid] [uniqueidentifier] NULL,
[photographerid] [uniqueidentifier] NULL,
[Isactive] [bit] NULL,
[WorkforFree] [bit] NULL,
[NoOfViews] [int] NULL,
[Description] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tbl_Model_N] PRIMARY KEY CLUSTERED
(
[Modelid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[tbl_modelavailablefor](
[model_id] [uniqueidentifier] NOT NULL,
[model_availablefor] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tbl_Vote](
[Voteid] [int] IDENTITY(1,1) NOT NULL,
[Modelid] [uniqueidentifier] NOT NULL,
[Rank1] [int] NULL,
[Rank2] [int] NULL,
[Rank3] [int] NULL,
[Rank4] [int] NULL,
[Rank5] [int] NULL,
[IpAddress] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SubmissionDate] [datetime] NOT NULL,
CONSTRAINT [PK_tbl_Vote_N] PRIMARY KEY CLUSTERED
(
[Voteid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[tbl_Feedback](
[Feedbackid] [int] IDENTITY(1,1) NOT NULL,
[Modelid] [uniqueidentifier] NOT NULL,
[Comments] [nvarchar](3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Q1] [int] NULL,
[Q2] [int] NULL,
[Q3] [int] NULL,
[Q4] [int] NULL,
[Q5] [int] NULL,
[Userid] [uniqueidentifier] NULL,
[IsApproved] [bit] NULL,
[Isactive] [bit] NULL,
[SubmissionDate] [datetime] NOT NULL,
CONSTRAINT [PK_tbl_Feedback] PRIMARY KEY CLUSTERED
(
[Feedbackid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Following are the search options: Categories, First name, Last name, Age ( @from - @to ) , Gender, Eye color, Hair Color, Work For Free(bit), Post Code, State, Photographerid, agencyid. Then there are sorting options : Most Voted, Highest Feedback, Browse Latest, Most Viewed.
I have already posted the Stored Proc. I will be glad to hear suggestions and help from the experts
Thanks