Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

recursive query Expand / Collapse
Author
Message
Posted Wednesday, November 5, 2008 7:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, November 5, 2008 7:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #597820
Posted Wednesday, November 5, 2008 8:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, November 5, 2008 9:46 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 7, 2011 1:41 AM
Points: 346, Visits: 534
tspot (11/5/2008)
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


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
Posted Thursday, November 6, 2008 2:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 23, 2014 3:29 AM
Points: 1,678, Visits: 19,554
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
Posted Thursday, November 6, 2008 8:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, November 6, 2008 8:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse