java两个表连接,连接两个一对多表重复记录

我有3张 table ， Transaction, Transaction_Items and Transaction_History .

Transaction 是父表的位置，而 Transaction_Items and Transaction_History 是子表，其中 one to many relationship .

当我尝试将这些表连接在一起时，如果我有 2+ Transaction_History 记录，或 2+ Transaction_Items 我得到重复或三重记录结果 .

这是我目前使用的SQL查询有效，但令我担心的是，将来如果我必须加入另一个 one-to-many 表，它将再次复制结果 .

I found a workaround for this, but i was just wondering if there is a better and cleaner way to do this ?

结果应该是PostgreSQL JSON数组，它将包含Transaction_Items和Transaction_History

SELECT

TR.id AS transaction_id,

TR.transaction_number,

TR.status,

TR.status AS status,

to_json(TR_INV.list),

COUNT(TR_INV) item_cnt,

COUNT(THR) tr_cnt,

json_agg(THR)

FROM transaction_transaction AS TR

LEFT JOIN (

SELECT

array_agg(t) list, -- this is a workaround method

t.transaction_id

FROM (

SELECT

TR_INV.transaction_id transaction_id,

IT.id,

IT.stock_number,

CAT.key category_key,

ITP.description description,

ITP.serial_number serial_number,

ITP.color color,

ITP.manufacturer manufacturer,

ITP.inventory_model inventory_model,

ITP.average_cost average_cost,

ITP.location_in_store location_in_store,

ITP.firearm_caliber firearm_caliber,

ITP.federal_firearm_number federal_firearm_number,

ITP.sold_price sold_price

FROM transaction_transaction_item TR_INV

LEFT JOIN inventory_item IT ON IT.id = TR_INV.item_id

LEFT JOIN inventory_itemprofile ITP ON ITP.id = IT.current_profile_id

LEFT JOIN inventory_category CAT ON CAT.id = ITP.category_id

LEFT JOIN inventory_categorytype CAT_T ON CAT_T.id = CAT.category_type_id

) t

GROUP BY t.transaction_id

) TR_INV ON TR_INV.transaction_id = TR.id

LEFT JOIN transaction_transactionhistory THR ON THR.transaction_id = TR.id

AND (THR.audit_code_id = 44 OR THR.audit_code_id = 27 OR THR.audit_code_id = 28)

WHERE TR.store_id = 21

AND TR.transaction_type = 'Pawn_Loan' AND TR.date_made >= '2018-10-08'

GROUP BY TR.id, TR_INV.list