INNER Join tables in sql

Question

I have two tables which I want to join together.

TABLE_A:

+--------+-----------+---------+
| row_id | category  | val_1   |
+--------+-----------+---------+
| 1      | cat1      | 6.5     |
| 1      | test      | 6.5     |
| 1      | dog1      | 2.1     |
+--------+-----------+---------+

TABLE_B:

+-----------+-----------+
| row_id    | val_2     |
+-----------+-----------+
| 1         | 2.0       |
+-----------+-----------+

When I use INNER JOIN, get results like this:

+-----------+-----------+-----------+------------+
| row_id    | category  | val_1     | val_2      |
+-----------+-----------+-----------+------------+
| 1         | cat1      | 6.5       | 2.0        |
| 1         | test      | 6.5       | 2.0        |
| 1         | dog1      | 2.1       | 2.0        |
+-----------+-----------+-----------+------------+

But I do not want have duplicated result of Val_2. I want results like this:

+-----------+-----------+-----------+------------+
| row_id    | category  | val_1     | val_2      |
+-----------+-----------+-----------+------------+
| 1         | cat1      | 6.5       | 2.0        |
| 1         | test      | 6.5       | NULL       |
| 1         | dog1      | 2.1       | NULL       |
+-----------+-----------+-----------+------------+

I'd really appreciate it if someone could help me.

Answer 1

Would this work, Mike? This may not be exactly what you need but you can tweak as needed.

SELECT 
    TABLE_A.*,
    CASE ROW_NUMBER() OVER(PARTITION BY TABLE_B.val_2 ORDER BY TABLE_A.row_id DESC) 
        WHEN 1 THEN TABLE_B.val_2
        ELSE NULL
    END as val_2
FROM
    TABLE_A
INNER JOIN
    TABLE_B ON  TABLE_A.row_id = TABLE_B.row_id
ORDER BY
    TABLE_A.Row_id