Help required in query

Database

Help required in query

Hi,

My requirement is as given below:

My table contains three columns:

start_date,id, prev_date

--------------------------------

1-NOV-2011,1,1-NOV-2010

1-NOV-2010,2,1-NOV-2009

1-NOV-2009,3,1-NOV-2008

1-NOV-2008,3,1-NOV-2007

2-OCT-2010,4,3-OCT-2009

From the above data, i need the below as output:

start_date,id,start_date1,id1,start_date2,id2

--------------------------------------------------------------

1-NOV-2011,1,1-NOV-2010,2,1-NOV-2009,3

The logic is,first i need to filter out the records whose start_date is in current year.For those records, i need to match the prev_date with the start_date of remaining columns.

I need to bring those in same record.

i dont want to compare more  than 2 hierarchies.

Thats why the result  doesn't contain 2008 record even though the matching start date is there.

Please help me

Thanks

1 REPLY
Enthusiast

Re: Help required in query

Pandeesh ,

              Use the below query and check whether its satifying your conditions.

SELECT

    A.start_date,

    A.id AS id

    ,B.start_date AS start_date1

    ,B.id AS Id1

    ,C.start_date AS start_date3

    ,C.id AS Id2

    FROM 

    <DBname>.<tablename> A

    INNER JOIN

    <DBname>.<tablename> B

    ON A.prev_date=B.start_date 

    INNER JOIN

    <DBname>.<tablename> C

    ON B.prev_date=C.start_date 

    WHERE EXTRACT(YEAR FROM A.start_date)=EXTRACT(YEAR FROM DATE )

    ;

If you wish to go through more hierarchies then its better to go with a recursive query.

Cheers,

Amir