SELECT DATA from many to many tables without repetead

fez226 0
Viewed 56

i have this project where i need to show this table on a webpage:

Result Table (one Bill can have one or many PurchaseOrder)

BILLID DESCRIPTION PO PO_DESCRIPTION HES
1 BILLING1 1001,1002 TEST1,TEST2 ABC,DFG
2 BILLING2 1003 TEST3 HIJ

Which its created based on 4 tables:

BillingTable

BILLID DESCRIPTION CREATEDAT
1 BILLING1 2022-01-03
2 BILLING2 2022-01-14

BillingPos Table (it had a relationship with BillingTable (many-to-one) and with HesTable (one to many)

ID FK_BILLID FK_HES_ID CREATEDAT
1 1 1 2022-01-03
2 1 2 2022-01-03
3 2 3 2022-01-14

Hes Table: it have a relationship with PurchaseOrder Table

ID HES_ID FK_PO_ID CREATEDAT
1 ABC 1 2022-01-01
2 DFG 2 2022-01-01
3 HIJ 3 2022-01-01

PurchaseOrder Table

ID PURCHASEORDER DESCRIPTION CREATEDAT
1 1001 TEST1 2022-01-01
2 1002 TEST2 2022-01-01
3 1003 TEST3 2022-01-01

I Know i have to use GROUP_CONCAT to concatenate the POs, Description and HES in one row for the billing id but i can´t figure it out

mysql sql node.js
1 Answers

Consider:

SELECT Billing.BillID, Billing.Description, Group_Concat(PurchaseOrder) AS PO,
Group_Concat(PurchaseOrder.Description) AS PO_Description, Group_Concat(HES_ID) AS HES
FROM Billing INNER JOIN (PurchaseOrder 
INNER JOIN (HES INNER JOIN BillingPos 
ON HES.ID = BillingPos.FK_HES_ID) 
ON PurchaseOrder.ID = HES.FK_PO_ID) 
ON Billing.BillID = BillingPos.FK_BilledID
GROUP BY BillID, Billing.Description;
June70
Related