May 21, 2012 at 12:12 am
he i am having 3 table i need to write 2 select query depend upon the @table3
declare @table1 table
(
catid int,categoryname varchar(100)
)
insert into @table1
select 1,'cricket' union all
select 2,'football'
declare @table2 table
(
sid int,catid int,subcatgoryname varchar(100)
)
insert into @table2
select 1,1,'bowling' union all
select 2,1,'batting' union all
select 3,1,'fielding' union all
select 4,2,'foul' union all
select 5,2,'goal'
declare @table3 table
(
adforumid int,memberid int,sid int,adforumname varchar(100),activesstatus bit
)
insert into @table3
select 1,1,4,'inform',1 union all
select 2,1,5,'serg',1 union all
select 3,1,4,'serinform',1 union all
select 4,2,4,'inform',1 union all
select 5,3,1,'serddd',1 union all
select 6,3,2,'serinform',1
from this @table3 expecting out put like this for the first select query
categoryname categoryadcount
cricket 2
football 4
from this @table3 expecting out put like this for the second select query
subcatgoryname subcatgoryadcount
foul 3
goal 1
bowling 1
batting 1
i just tried this but iam not getting the correct output
select distinct(sid) from @table3
SELECT
CSF.sid,
SB.SubCategoryName
FROM
@table1 AD
JOIN
@table2 SB ON SB.catid=AD.catid
LEFT OUTER JOIN
@table3 CSF ON CSF.sid=SB.sid
can any one plz tell me how to solve this
May 21, 2012 at 12:52 am
Still learning posting in proper format 🙂
--these should give u desired results
--first query
select t1.categoryname ,count(*) categorycount from @table1 t1
inner join @table2 t2
on t1.catid= t2.catid
inner join @table3 t3
on t2.sid = t3.sid
group by t1.categoryname
--second query
select t1.subcatgoryname ,count(*) subcatgorycount
from @table2 t2
inner join @table3 t3
on t2.sid = t3.sid
group by t2.subcatgoryname
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 21, 2012 at 2:48 am
Gullimeel (5/21/2012)
these should give u desired results--first query
select t1.categoryname ,count(*) categorycount from @table1 t1
inner join @table2 t2
on t1.catid= t2.catid
inner join @table3 t3
on t2.sid = t3.sid
group by t1.categoryname
--second query
select t1.subcatgoryname ,count(*) subcatgorycount
from @table2 t2
inner join @table3 t3
on t2.sid = t3.sid
group by t2.subcatgoryname
+1 Gullimeel.
But, since the OP posted the DDL and sample data so well.....you could have used SQL quotes as well....:-)
May 21, 2012 at 11:04 pm
hi
Gullimeel
our query was working fine but if want to
get the out put like this mean what i want to do
subcatgoryname subcatgoryadcount
bowling 1
batting 1
fielding 0
foul 3
goal 1
sorry for the inconvenience Gullimeel
i just tried like this
--second query
select t2.sid,t2.subcatgoryname ,count(*) subcatgorycount
from @table2 t2
left outer join @table3 t3
on t2.sid = t3.sid
group by t2.sid, t2.subcatgoryname
i am just trying to get null count also
May 21, 2012 at 11:21 pm
select t2.sid,t2.subcatgoryname ,count(t3.sid) subcategorycount
from @table2 t2
left outer join @table3 t3
on t2.sid = t3.sid
group by t2.sid, t2.subcatgoryname
Try this...
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 21, 2012 at 11:21 pm
sivag (5/21/2012)
hiGullimeel
our query was working fine but if want to
get the out put like this mean what i want to do
subcatgoryname subcatgoryadcount
bowling 1
batting 1
fielding 0
foul 3
goal 1
sorry for the inconvenience Gullimeel
i just tried like this
--second query
select t2.sid,t2.subcatgoryname ,count(*) subcatgorycount
from @table2 t2
left outer join @table3 t3
on t2.sid = t3.sid
group by t2.sid, t2.subcatgoryname
i am just trying to get null count also
You can use Left Join to get the desired result as follows:
select t2.subcatgoryname ,count(t3.adforumname) As subcatgorycount
from @table2 t2
Left join @table3 t3
on t2.sid = t3.sid
group by t2.subcatgoryname
Hope this helps.
May 22, 2012 at 2:38 am
thanks a lot Gullimeel and Vinu Vijayan
May 22, 2012 at 4:29 am
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply