How to remove duplicate values from SQL inner join tables?

Question

I have two tables:

Table 1:

+-----------+-----------+------------------+
| ID        | Value     | other            |
+-----------+-----------+------------------+
| 123456    | 5         | 12               |
| 987654    | 7         | 15               |
| 456789    | 6         | 22               |
+-----------+-----------+------------------+

Table 2:

+-----------+-----------+------------------+
| ID        | Type      | other            |
+-----------+-----------+------------------+
| 123456    | 00        | 2                |
| 123456    | 01        | 6                |
| 123456    | 02        | 4                |
| 987654    | 00        | 7                |
| 987654    | 01        | 8                |
| 456789    | 00        | 6                |
| 456789    | 01        | 16               |
+-----------+-----------+------------------+

Now I perform the inner join:

SELECT
  table1.ID, table2.TYPE, table1.value, table2.other 
FROM
  table1 INNER JOIN table2 ON table1.ID = table2.ID

Here the SQLfiddle

Result Table:

+-----------+-----------+---------+------------------+
| ID        | Type      | Value   | other            |
+-----------+-----------+---------+------------------+
| 123456    | 00        | 5       | 2                |
| 123456    | 01        | 5       | 6                |
| 123456    | 02        | 5       | 4                |
| 987654    | 00        | 7       | 7                |
| 987654    | 01        | 7       | 8                |
| 456789    | 00        | 6       | 6                |
| 456789    | 01        | 6       | 16               |
+-----------+-----------+---------+------------------+

This is totally what I expected but not what I need. Because if I now want to get the Value per ID the Value gets doubled or tripled for the first cause.

Desired Table:

+-----------+-----------+---------+------------------+
| ID        | Type      | Value   | other            |
+-----------+-----------+---------+------------------+
| 123456    | 00        | 5       | 2                |
| 123456    | 01        | -       | 6                |
| 123456    | 02        | -       | 4                |
| 987654    | 00        | 7       | 7                |
| 987654    | 01        | -       | 8                |
| 456789    | 00        | 6       | 6                |
| 456789    | 01        | -       | 16               |
+-----------+-----------+---------+------------------+

I tried to achieve a similar output by counting the rows per id and dividing the sum of Value by that count but it did not seem to work and is not the desired output.

Also, I tried grouping but this did not seem to achieve the desired output.

One thing to mention is that the DB I am working with is an ORACLE SQL DB.

Answer 1

How about this:

select table1.id
     , table2.type
     , case
           when row_number() over (partition by table1.id order by table2.type) = 1
           then table1.value
       end as "VALUE"
     , table2.other
from   table1
       join table2 on table1.id = table2.id
order by 1, 2;

(This is Oracle SQL syntax. Your SQL Fiddle (thanks!) was set to MySQL, which as far as I know doesn't have analytic functions like row_number().)