Postgresql with recursive query

Question

After trying with with recursion I have question

I am trying with two table

first table is complain_table

complain                   product_id
----------------------------------------
Not working          -          1
not working                     1
not working                     1
Loading problem                 2
Loading problem                 2

second table product_table

Name     id                      
--------------
usb      1
cd       2

Now my desired output is

product                complain
-----------------------------------
usb                   Not working
                      Not working
                      Not working
cd                    Loading problem
                      Loading problem

What did you already try? PostgreSQL version? How is this different to stackoverflow.com/q/19872751/398670 ? Did you follow the advice given there? Format your questions please. This doesn't require recursion at all, it should just be a GROUP BY and some string_agg use. — Craig Ringer
– Craig Ringer, Commented Nov 11, 2013 at 7:28
select p.product from ((with cte(product,sort, sort2,id) as ( select distinct pp.name,pp.name,0,count(pp.name) from product_complaints pc left join product_product pp on pp.id = pc.complaints_id group by pp.name,pc.complaints_id union all select ' ' || pc.name,pp.name,1,pc.product_id from product_complaints pc left join product_product pp on pp.id = pc.complaints_id group by pp.name,pc.product_id,pc.name ) select product,sort,sort2,id from cte group by product,sort,sort2,id order by sort,sort2,id)) as p — Shyam Solanki
– Shyam Solanki, Commented Nov 11, 2013 at 7:36
Please don't post code in comments, edit your question instead. — user330315
– user330315, Commented Nov 11, 2013 at 7:52

user330315 · Accepted Answer · 2013-11-11 08:00:19Z

1

If you want the output as shown (suppressing the repeating product names), the following should do it:

select case 
         when row_number() over (partition by p.name order by ct.complain) = 1 then p.name
         else null
       end as product,
       ct.complain
from products p
  join complain_table ct on p.product_id = ct.product_id
order by p.product_id;

Btw: your complain_table looks like it should have a complain_reason_id referencing a complain_reason_text table to avoid repeating the same complain text over and over again

answered Nov 11, 2013 at 8:00

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql with recursive query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related