Jump to content
VJS

HELP with JOIN

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

Share this post


Link to post
Share on other sites

I'm feeling a more design issue is afoot here. Why do you need the extras removed?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...