Need of query - 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

Need of query

Author

Topic

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-24 : 05:10:34

hi friends,

I am using 3 tables namely category,reference,referencehistory

In category table the fields are catid,catname,parentid

In reference table the fields are refid,catid,avgrating

In referencehistory table the fields are linkid,refid,title,url,desc

from the above i need to display the catname,subcatname,title,url,desc,avgrating

In category table parentid is 0 then it is considered as category, else it is subcategory.

ranganath
Posting Yak Master

209 Posts

Posted - 2008-10-24 : 05:18:02

Hi,

use CTE

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-24 : 05:34:56

whats CTE

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-24 : 05:43:02

quote:
Originally posted by vidhya

whats CTE

Google for CTE+sql server

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 05:43:25

CTE is common table expression. Its a new feature avialble in sql 2005 which enables you write recursive queries. see below

http://msdn.microsoft.com/en-us/library/ms186243.aspx

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-24 : 05:48:18

i did not have any idea about it. now i need query for my requirement.

ranganath
Posting Yak Master

209 Posts

Posted - 2008-10-24 : 06:00:18

Hi,

may be helpful to u .

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89335&SearchTerms=CTE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 06:00:19

quote:
Originally posted by vidhya

i did not have any idea about it. now i need query for my requirement.

You have a very good example with explanation in link. try it out yourself and post when you face any problem.

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-24 : 09:18:14

select c.name,h.title,h.url,h.description,r.avgrating from kr_category c,kr_reference r,kr_referencehistory h
where c.catid =r.catid and h.refid= r.refid and (c.name like '%s%' or h.title like '%s%' or h.url like '%s%' or h.description like '%s%')

I used this query, but i need to display the category name if parentid is 0 and subcategory not parent id 0. but the catname is single field which contains categoryname and subcategoryname

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 09:54:53

Something like:-

;With CTE(title,url,desc,avgrating,catname,catid) AS
(
SELECT rc.title,rc.url,rc.desc,r.avgrating,c.catname,c.catid
FROM category c
JOIN reference r
ON r.catid=c.catid
JOIN referencehistory  rc
ON rc.refid=r.refid
WHERE c.parentid=0

UNION ALL

SELECT rc.title,rc.url,rc.desc,r.avgrating,c.catname
FROM category c
JOIN reference r
ON r.catid=c.catid
JOIN referencehistory  rc
ON rc.refid=r.refid
JOIN CTE c1
ON c1.catid=c.parentid
)
SELECT * FROM CTE

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-24 : 10:17:46

With CTE(title,url,description,avgrating,name,catid) AS
(
SELECT rc.title,rc.url,rc.description,r.avgrating,c.name,c.catid
FROM kr_category c
JOIN kr_reference r
ON r.catid=c.catid
JOIN kr_referencehistory rc
ON rc.refid=r.refid
WHERE c.parentid=0

UNION ALL

SELECT rc.title,rc.url,rc.description,r.avgrating,c.name
FROM kr_category c
JOIN kr_reference r
ON r.catid=c.catid
JOIN kr_referencehistory rc
ON rc.refid=r.refid
JOIN CTE c1
ON c1.catid=c.parentid
)

I used the above query, it shows error "Incorrect syntax near the keyword 'With'."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-24 : 10:20:04

Are you using SQL Server 2005?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 10:25:33

quote:
Originally posted by vidhya

With CTE(title,url,description,avgrating,name,catid) AS
(
SELECT rc.title,rc.url,rc.description,r.avgrating,c.name,c.catid
FROM kr_category c
JOIN kr_reference r
ON r.catid=c.catid
JOIN kr_referencehistory rc
ON rc.refid=r.refid
WHERE c.parentid=0

UNION ALL

SELECT rc.title,rc.url,rc.description,r.avgrating,c.name
FROM kr_category c
JOIN kr_reference r
ON r.catid=c.catid
JOIN kr_referencehistory rc
ON rc.refid=r.refid
JOIN CTE c1
ON c1.catid=c.parentid
)

I used the above query, it shows error "Incorrect syntax near the keyword 'With'."

1. are you using this as first statement in batch ? or is this part of a query?
2. Did you notice ; i gave on left side of With?
3. Are you using SQL 2005 with compatibility level of 90?

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-24 : 10:30:18

i notice that ; if i use ";" it shows error "Line 1: Incorrect syntax near ';'." so i removed that.
I am using sql 2000.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 10:42:34

CTE is not available in SQL 2000. use method in link below instead

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109492&SearchTerms=hierarchy

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-24 : 11:38:54

in category table i am having 3 fields namely

catid,catname,parentid.
1 Technology 0
2 hardware 1
3 software 1
4 Social 0

The need to display category and subcategory from above table

result
category subcategory
Technology hardware
Social software

Like this i need.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources