recursive query

  • [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]

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

  • 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

  • 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/61537
  • The ID in table2 is the same ID with Table1->ID (not parent)

    BTW, Mark has solved this issue

  • 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