sql - How to merge the records from two queries each with unique fields having numeric data and avoiding doubling of numeric data -

i have 2 sql queries. similar, identical exception of 1 clause in each. each query sums sale_amount field , aliases different name 1 sales , 1 adjustments. need merge records both based on contract(c).id , transaction(t).line_id. problem i'm having when there matching ids , both queries matching have data in respective dollar-based field, dollar amount second query's field(sales) doubles.

i've seen other posts this, seem resolve problem using left join. need retain data in both tables, regardless of match well(outer join). below 2 individual queries.

// query 1 select c.id      t.line_id      t.date      u.name      a.name      sum(t.sale_amount) adjustments invoice join contract c on c.contract_no = i.contract_no join transaction t on t.invoice_no = i.invoice_no join unit u on u.unit_no = t.unit_no join agency on a.agency_no = i.agency_no t.unit_no in (44)      , i.hard_invoice = 'y'      , t.code in ('new', 'edit')      , t.adjustment_no not null group c.id, t.date, u.name, a.name, t.line_id  // query 2 select c.id      t.line_id      t.date      u.name      a.name      sum(t.sale_amount) sales invoice join contract c on c.contract_no = i.contract_no join transaction t on t.invoice_no = i.invoice_no join unit u on u.unit_no = t.unit_no join agency on a.agency_no = i.agency_no t.unit_no in (44)      , i.hard_invoice = 'y'      , t.code in ('new', 'edit')      , t.adjustment_no null group c.id, t.date, u.name, a.name, t.line_id 

you use conditional aggregation:

select c.id, t.line_id, t.date, u.name, a.name,        sum(case when t.adjustment_no not null t.sale_amount else 0 end) adjustment,        sum(case when t.adjustment_no null t.sale_amount else 0 end) sales invoice join      contract c      on c.contract_no = i.contract_no join      transaction t      on t.invoice_no = i.invoice_no join      unit u      on u.unit_no = t.unit_no join      agency      on a.agency_no = i.agency_no t.unit_no in (44) ,       i.hard_invoice = 'y' ,       t.code in ('new', 'edit') group c.id, t.date, u.name, a.name, t.line_id;