All Forums General
TeradataUsr 3 posts Joined 08/14
07 Aug 2014
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

TeradataUsr 3 posts Joined 08/14
11 Aug 2014

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

CarlosAL 512 posts Joined 04/08
11 Aug 2014

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.
 

TeradataUsr 3 posts Joined 08/14
11 Aug 2014

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

You must sign in to leave a comment.