SQL Merging rows with specific matching columns

I'm trying to handle this issue in my code that should be handled in SQL. The problem is I want to take this

ID COL 1 | ID COL 2 | CHARGE | PAYMENT

 2    |       3    |    17    |      0
 2    |       3    |    0     |      17

and turn it into this

ID COL 1 | ID COL 2 | CHARGE | PAYMENT

 2    |       3    |    17    |    17

table1

id | whatever | whatever1

5 | null | null

table2

id | id col 1 | id col 2 | charge | payment

5 | 2 | 3 | 17 | 0

5 | 2 | 3 | 0 | 17

current result:

id | whatever | whatever1 | idcol1 | idcol2 | charge | payment

5 | null | null | 2 | 3 | 17 | 0

5 | null | null | 2 | 3 | 0 | 17

want:

id | whatever | whatever1 | idcol1 | idcol2 | charge | payment

5 | 2 | 3 | 17 | 17

The problem is during my sql call I'm doing an inner join, which does a cartesian product for some of the values rather than doing what I'd want above. Does anyone have an idea how this could be accomplished?

Answers


You can use group by for this:

select IDCOL1, IDCOL2, max(CHARGE) as charge, max(PAYMENT) as payment
from table t
group by idcol1, idcol2

This gets the maximum charge and maximum payment from all rows with the same id columns. If you have more than one row with charges or payment, you might prefer SUM() to MAX().

With the joins, this would look like:

select t1.id, t1.whatever, t1.whatever, t2.IDCOL1, t2.IDCOL2,
       max(CHARGE) as charge, max(PAYMENT) as payment
from table1 t1 join
     table2 t2
     on t1.id = t2.id
group by t1.id, t1.whatever, t1.whatever, t2.IDCOL1, t2.IDCOL2

Need Your Help

How does one get the DispatchContext in Ofbiz from a Java program

java ofbiz

I've spent the last few hours trying to find the answer in the "Apache Ofbiz Cookbook" and "Apache Ofbiz Development: The Beginner's Tutorial" how to get the DispatchContext in Ofbiz from a Java me...

Toggle element visibility via radio select

javascript onclick

This form has a hidden textara and a visible textbox. I would like to swap visibility of these elements if option "D:" is selected, but not sure how to correctly check which radio button is checked...