Jump to content

HELP with JOIN


VJS
 Share

Recommended Posts

I am joining 2 tables using the query below.  Its many to many relationship.

The join works fine but creates multiple rows for each item which is expected but the Amount/value column is also duplicated. How to avoid this? Thanks As you can see the Current output the value (50000 appears 3 times instead of 1 and 27000 appears 3 times instead of 1)

SELECT * FROM T1
INNER JOIN T2 ON
T1.Projectex = T2.WBS_Parent

 

Table 1:

+-----------+------------+-----------+----------+--------+----------+-------+
| Projectex | CAPEX_OPEX | costelmnt | sap_vers | Period | FISCYEAR | VALUE |
+-----------+------------+-----------+----------+--------+----------+-------+
| 0-01081   | CAPEX      |      3416 |       61 |      3 |     2020 | 50000 |
| 0-01081   | OPEX       |      7077 |       30 |      5 |     2020 | 27000 |
+-----------+------------+-----------+----------+--------+----------+-------+

Table2:

+------------+-----------------+---------+---------+
| WBS_PARENT | FINANCILAL_YEAR | MEASURE | AMOUNT  |
+------------+-----------------+---------+---------+
| 0-01081    |            2020 | CPX     | 2000000 |
| 0-01081    |            2020 | OPX     |   50000 |
| 0-01081    |            2020 | OPX     | 1000000 |
+------------+-----------------+---------+---------+

CURRENT OUTPUT:

Projectex| CAPEX_OPEX|  costelmnt|  sap_vers|  Period|  FISCYEAR|  VALUE|  
0   0-01081|      CAPEX|       3416|       61|      3|      2020|  50000|   
1   0-01081|      CAPEX|       3416|       61|      3|      2020|  50000|   
2   0-01081|      CAPEX|       3416|       61|      3|      2020|  50000|  
3   0-01081|       OPEX|       7077|       30|      5|      2020|  27000|  
4   0-01081|       OPEX|       7077|       30|      5|      2020|  27000|   
5   0-01081|       OPEX|       7077|       30|      5|      2020|  27000|   

  WBS_PARENT  FINANCILAL_YEAR MEASURE   AMOUNT  
0    0-01081|            2020|     CPX|  2000000  
1    0-01081|            2020|     OPX|    50000  
2    0-01081|            2020|     OPX|  1000000  
3    0-01081|            2020|     CPX|  2000000  
4    0-01081|            2020|     OPX|    50000  
5    0-01081|            2020|     OPX|  1000000  

Expected Output:

Projectex| CAPEX_OPEX| costelmnt| sap_vers| Period| FISCYEAR| VALUE|
0 0-01081| CAPEX| 3416| 61| 3| 2020| 50000|
1 0-01081| CAPEX| 3416| 61| 3| 2020|
2 0-01081| CAPEX| 3416| 61| 3| 2020|
3 0-01081| OPEX| 7077| 30| 5| 2020| 27000|
4 0-01081| OPEX| 7077| 30| 5| 2020|
5 0-01081| OPEX| 7077| 30| 5| 2020|

WBS_PARENT FINANCILAL_YEAR MEASURE AMOUNT
0 0-01081| 2020| CPX| 2000000
1 0-01081| 2020| OPX| 50000
2 0-01081| 2020| OPX| 1000000
3 0-01081| 2020| CPX|
4 0-01081| 2020| OPX|
5 0-01081| 2020| OPX|

 

sql query.jpg

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...