joining two queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

joining two queries

Author

Topic

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-25 : 01:59:45

hi friends,
I am having 3 tables namely kr_reference, kr_category, kr_referencehistory.
In kr_reference table i am having the fields refid,catid
In kr_category table i am having the fields catid,catname,parentid
In kr_referencehistory i am having the fields linkid,refid,title,url,avgrating.

From the above tables i need to display refid, categoryname, subcategory, title

i used below query to display category,subcategory

SELECT t1.name as t1, t2.name as t2 FROM kr_category t1
LEFT JOIN kr_category t2 ON t1.catid=t2.parentid
WHERE t1.parentid=0

i used the below query to display the refid, title

select h.title as title,r.refid as refid from kr_reference r inner join kr_category c on c.catid=r.catid and r.deleted=0 and c.parentid=6 inner join kr_referencehistory h on h.refid=r.refid
and h.status=1 left join kr_referencerating rf on r.refid=rf.refid

I need to combine two queries to get my expected output
refid,title,category,subcategory.

Thanks in Advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 02:05:21

did you try method in link i posted earlier

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113202

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-10-25 : 02:24:08

Try this:

select r.refid as refid, a.catname as categoryname,b.catname as subcategory,rh.title as title
from kr_category a inner join kr_category b on a.catid=b.parentid
inner join kr_reference r on r.catid=b.catid
inner join kr_referencehistory rh on rh.refid=r.refid

malay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 02:26:07

quote:
Originally posted by malaytech2008

Try this:

select r.refid as refid, a.catname as categoryname,b.catname as subcategory,rh.title as title
from kr_category a inner join kr_category b on a.catid=b.parentid
inner join kr_reference r on r.catid=b.catid
inner join kr_referencehistory rh on rh.refid=r.refid

malay

your solution will work only for 1 level. what if record has a child which has child and so on...

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-10-25 : 02:37:19

According to you,root category name should show with grand child category name.

malay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 03:13:55

[code]declare @t table (parentid int,childid int,parent varchar(20), child varchar(20), lev int, fullpath varchar(1000))
declare @lev int
set @lev = 0

--Get Root node(s)
insert @t (parentid,childid, parent, child, lev, fullpath)
select null,catid,null, catname, @lev, catname
from kr_category
where parentid=0

while @@rowcount > 0
begin
set @lev = @lev + 1

--Get all children of current level
insert @t (parentid, childid,parent,child, lev, fullpath)
select c.parentid, c.catid,t.catname,c.catname, @lev, t.fullpath + '.' + c.catname
from @t t
join kr_category c on c.parentid = t.childid and t.lev = @lev-1
left join @t x on x.parentid = c.parentid and x.childid = c.catid
where x.parentid is null
end

select * from @t order by fullpath

select * from @t t
join kr_reference r
on t.childid=r.catid
and r.deleted=0
and t.parentid=6
join kr_referencehistory rh
on rh.refid=r.refid
and rh.status=1
left join kr_referencerating rf
on r.refid=rf.refid
order by fullpath[/code]

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-25 : 03:37:02

visakh,

i am getting error Server: Msg 207, Level 16, State 3, Line 16
Invalid column name 'catname'.

catname is in kr_category table but why it show error.

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-25 : 03:38:24

malay,

You are absolutely correct.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 03:57:00

try this small modification

declare @t table (parentid int,childid int,parent varchar(20), child varchar(20), lev int, fullpath varchar(1000))
declare @lev int
set @lev = 0

--Get Root node(s)
insert @t (parentid,childid, parent, child, lev, fullpath)
select null,catid,null, catname, @lev, catname
from   kr_category
where  parentid=0

while @@rowcount > 0
begin
       set @lev = @lev + 1

       --Get all children of current level
       insert @t (parentid, childid,parent,child, lev, fullpath)
       select c.parentid, c.catid,t.child,c.catname, @lev, t.fullpath + '.' + c.catname
       from   @t t
       join   kr_category c on c.parentid = t.childid and t.lev = @lev-1
       left join @t x on x.parentid = c.parentid and x.childid = c.catid
where x.parentid is null
end


select * from @t  order by fullpath

select * from @t  t
join kr_reference r
on t.childid=r.catid
and r.deleted=0 
and t.parentid=6
join kr_referencehistory rh
on rh.refid=r.refid
and rh.status=1
left join kr_referencerating rf 
on r.refid=rf.refid 
order by fullpath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources