InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 19:11:07 GMT20http://www.sqlservercentral.com/Forums/Topic597814-338-1.aspxHi Mark,It works...thank you very much for the answer and your timergds,richardThu, 06 Nov 2008 20:51:37 GMTtspothttp://www.sqlservercentral.com/Forums/Topic597814-338-1.aspxThe ID in table2 is the same ID with Table1->ID (not parent)BTW, Mark has solved this issueThu, 06 Nov 2008 20:45:41 GMTtspothttp://www.sqlservercentral.com/Forums/Topic597814-338-1.aspx[code]WITH CTE AS (SELECT ID,NAME AS DEPTFROM TABLE1WHERE PARENTID=-1UNION ALLSELECT t.ID,c.DEPTFROM CTE cINNER JOIN TABLE1 t ON t.PARENTID=c.ID)SELECT t.DATE,c.DEPT,t.QTYFROM CTE cINNER JOIN TABLE2 t ON t.ID=c.IDORDER BY t.DATE,t.QTY[/code]Thu, 06 Nov 2008 02:37:03 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic597814-338-1.aspx[quote][b]tspot (11/5/2008)[/b][hr][font="Courier New"]Hi,I have these tables :TABLE1ID NAME PARENTID=== ========= ========1 SALES -12 MIS -13 LANG 14 RAMA 35 ANDRE 26 ROY 5 TABLE2DATE ID QTY===== === =====01/01 3 101/01 5 202/01 6 6 How to make a query for below result:DATE DEPT QTY--------------------01/01 SALES 101/01 MIS 202/01 MIS 6[/font][/quote]What is the criteria of joining between these two tables, i mean how can we relate these two tablesWed, 05 Nov 2008 21:46:48 GMTkrayknothttp://www.sqlservercentral.com/Forums/Topic597814-338-1.aspxBut if you see the data carefully the name that i want to display is department nameWed, 05 Nov 2008 20:24:45 GMTtspothttp://www.sqlservercentral.com/Forums/Topic597814-338-1.aspxThat won't be a recurrsive query... it'll be a simple join between the two tables joined on ID.Wed, 05 Nov 2008 19:44:00 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic597814-338-1.aspx[font="Courier New"]Hi,I have these tables :TABLE1ID NAME PARENTID=== ========= ========1 SALES -12 MIS -13 LANG 14 RAMA 35 ANDRE 26 ROY 5 TABLE2DATE ID QTY===== === =====01/01 3 101/01 5 202/01 6 6 How to make a query for below result:DATE DEPT QTY--------------------01/01 SALES 101/01 MIS 202/01 MIS 6[/font]Wed, 05 Nov 2008 19:30:27 GMTtspot