recursive query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » recursive query

recursive query

Rate Topic

Display Mode

Topic Options

Author

Message

tspot

Posted Wednesday, November 05, 2008 7:30 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, November 16, 2008 10:49 PM
Points: 4, Visits: 5

Hi,

I have these tables :

TABLE1
ID NAME PARENTID
=== ========= ========
1 SALES -1
2 MIS -1
3 LANG 1
4 RAMA 3
5 ANDRE 2
6 ROY 5

TABLE2
DATE ID QTY
===== === =====
01/01 3 1
01/01 5 2
02/01 6 6

How to make a query for below result:

DATE DEPT QTY
--------------------
01/01 SALES 1
01/01 MIS 2
02/01 MIS 6

Post #597814

Jeff Moden

Posted Wednesday, November 05, 2008 7:44 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910, Visits: 26,800

That won't be a recurrsive query... it'll be a simple join between the two tables joined on ID.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #597820

tspot

Posted Wednesday, November 05, 2008 8:24 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, November 16, 2008 10:49 PM
Points: 4, Visits: 5

But if you see the data carefully the name that i want to display is department name

Post #597836

krayknot

Posted Wednesday, November 05, 2008 9:46 PM

Old Hand

Group: General Forum Members
Last Login: Friday, October 07, 2011 1:41 AM
Points: 346, Visits: 534

tspot (11/5/2008)

What is the criteria of joining between these two tables, i mean how can we relate these two tables

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Post #597881

Mark-101232

Posted Thursday, November 06, 2008 2:37 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 10:29 AM
Points: 1,501, Visits: 18,198

WITH CTE AS (
SELECT ID,NAME AS DEPT
FROM TABLE1
WHERE PARENTID=-1
UNION ALL
SELECT t.ID,c.DEPT
FROM CTE c
INNER JOIN TABLE1 t ON t.PARENTID=c.ID)
SELECT t.DATE,c.DEPT,t.QTY
FROM CTE c
INNER JOIN TABLE2 t ON t.ID=c.ID
ORDER BY t.DATE,t.QTY

____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.

Post #597957

tspot

Posted Thursday, November 06, 2008 8:45 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, November 16, 2008 10:49 PM
Points: 4, Visits: 5

The ID in table2 is the same ID with Table1->ID (not parent)
BTW, Mark has solved this issue

Post #598646

tspot

Posted Thursday, November 06, 2008 8:51 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, November 16, 2008 10:49 PM
Points: 4, Visits: 5

Hi Mark,

It works...thank you very much for the answer and your time

rgds,
richard

Post #598649

« Prev Topic | Next Topic »

Permissions