selecting distinct values from master table and repeated values from joined tables

Question

I'm trying to select distinct values from master table and repeated values from child tables. I have 4 tables:

invoice
invoiceLine
product
businessPartner

My query:

select c_invoice.c_invoice_ID, c_bpartner.name as "Business Partner", M_Product.name as "Product", c_invoiceline.priceentered as "amount" from adempiere.c_invoice left join adempiere.c_invoiceline on c_invoice.c_invoice_ID=c_invoiceline.c_invoice_ID left join adempiere.M_Product on c_invoiceline.M_Product_ID =M_Product.M_Product_ID left join adempiere.C_BPartner on c_invoice.c_bpartner_ID=c_bpartner.c_bpartner_id where c_invoice.sh_booking_ID=1000019 and c_invoice.c_doctypetarget_id=1000005

My query result:

INVOICEID BUSINESS Partner PRODUCT AMT 1000005; "Tehmoor"; "Charge 1"; 1200 1000005; "Tehmoor"; "Standard"; 1500 1000006; "Rafay"; "Charge 1"; 1200 1000006; "Rafay"; "Standard"; 1100

and expected result

INVOICEID BUSINESS Partner PRODUCT AMT 1000005; "Tehmoor"; "Charge 1"; 1200 ; NULL; "Standard"; 1500 1000006; "Rafay"; "Charge 1"; 1200 ; NULL; "Standard"; 1100

you have "BUSINESS Partner" -eq null in second and fourth rows in expected result - why?.. — Vao Tsun
– Vao Tsun, Commented Aug 9, 2017 at 10:02
It's logical, since invoices are created for single business partners. — Laposhasú Acsa
– Laposhasú Acsa, Commented Aug 9, 2017 at 11:04

Laposhasú Acsa · Accepted Answer · 2017-08-09 10:57:32Z

You can try something like this:

select CASE WHEN row_number() OVER (PARTITION BY mast.id) = 1 THEN mast.title ELSE NULL END as title, joined.measure from mast left join joined on (mast_id = mast.id)

I've created a fiddle for it, so you can examine my example schema. I think it's better to handle this kind of requirement in a host language, because in SQL it's a bit tricky.

zarruq · Accepted Answer · 2017-08-09 11:21:30Z

I have reproduced your schema in my local environment. Below is the query using which you can achieve your desired result.

SELECT CASE WHEN (Rank() Over(ORDER BY i.c_invoice_id ASC)) = (Row_Number() Over (ORDER BY i.c_invoice_id ASC)) THEN pt.b_name ELSE NULL END AS "Business Partner", CASE WHEN (Rank() Over(ORDER BY i.c_invoice_id ASC)) = (Row_Number() Over (ORDER BY i.c_invoice_id ASC)) THEN i.c_invoice_id ELSE NULL END AS Invoice_Id, pr.b_name, il.price FROM invoice i LEFT JOIN c_invoice_line il ON il.c_invoice_id = il.c_invoice_id LEFT JOIN c_product pr ON il.product_line_id = pr.b_prod_id LEFT JOIN c_bpartner pt ON pt.b_partner_id = Trim(il.c_prod_id);

If required, change your table and column names accordingly.

Collectives™ on Stack Overflow

selecting distinct values from master table and repeated values from joined tables

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related