Recursive sql with Left outer join

General

Recursive sql with Left outer join

I have a query that is using Table1 and Table2 with left outer join on 'usage'. Now I have to join that query

with the Table3 with (I guess recursive sql) to generate the 'Resulting table'.

I saw lot of examples on recursive sql, but didnt find any thing that is using left outer join.

Now my existing query is like this

select PIN,  startDt,  StartTm,  usage,  Min from Table1 t1 left Outer join Table2 t2 on t1.usage= t2.usage;

How can I do the Table3 with this query, so that ratGrp will be in comma separated way? Please help!!

Table1

PIN  startDt  StartTm  usage  Min

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

123 08/03/2014 12:12:00 500  4567

234 08/04/2014 12:12:00 200  4568

.....

Table2

1stCol  2ndCol  usage

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

abc        234      500

Table3

PIN   ratGrp

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

123   3300

123  100

123  103

234  3300

234  550

Resulting table

PIN startDt  StartTm  usage  Min  ratGrp

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

123 08/03/2014 12:12:00 500  4567 3300,100,103

234 08/04/2014 12:12:00 200  4568 3300,550

Tags (2)
3 REPLIES

Re: Recursive sql with Left outer join

Des anybody has any suggestion on this? I am still struggling to fix this.

Re: Recursive sql with Left outer join

The logic is not clear, since Table2 is missing in the required 'Resulting table'.

'abc', '123', '234'... No DDL, no REAL SQL, not a single piece of the progress made...

Seems some kind homework to me...

Cheers.

Carlos.

Re: Recursive sql with Left outer join

I have a web page, where users can enter PIN and see

PIN,  startDt,  StartTm,  usage,  Min, ID in a tabular form. There is this new requirement where user want to see all ratGrp related to that PIN in a comma separated way. That information is in the table3.

I have modified TABLE1 and TABLE2.

I haven't put the actual table info here. Actual table looks something similar, but it is giant. I dont know how to PUT the DDL here.

Table1

PIN  startDt  StartTm  usage  Min

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

123 08/03/2014 12:12:00 200  4567

234 08/04/2014 12:12:00 500  4568

.....

Table2

ID    PIN   usage

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

abc  234  500

def  567  100

Table3

PIN   ratGrp

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

123   3300

123  100

123  103

234  3300

234  550

234  100

Resulting table

PIN startDt   StartTm   usage  Min  ID   ratGrp

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

234 08/04/2014 12:12:00 500  4568 abc  3300,550,100