November 5, 2008 at 7:30 pm
[font="Courier New"]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
[/font]
November 5, 2008 at 7:44 pm
That won't be a recurrsive query... it'll be a simple join between the two tables joined on ID.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2008 at 8:24 pm
But if you see the data carefully the name that i want to display is department name
November 5, 2008 at 9:46 pm
tspot (11/5/2008)
[font="Courier New"]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
[/font]
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
November 6, 2008 at 2:37 am
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
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537November 6, 2008 at 8:45 pm
The ID in table2 is the same ID with Table1->ID (not parent)
BTW, Mark has solved this issue
November 6, 2008 at 8:51 pm
Hi Mark,
It works...thank you very much for the answer and your time
rgds,
richard
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply