SQL Two joins; First one a left join; second only join when combination does not exist yet-CodePudding

I have the following problem. I have the following 3 tables, I can’t edit those. I have a "main" table I would like to join "Table1" to, I succeeded (see fiddle). The next step is to join "Table2”, but only when the Charge-Name combination does not exist yet. If it doesn’t exists add it and set Factor to 0. See table "Desired result" for my goal.

http://sqlfiddle.com/#!9/fc7409/1

Code:

SELECT  Main.Charge,
        Main.Id,
        Table1.Name,
        Table1.Factor
FROM Main
Left JOIN Table1
ON Main.Charge = Table1.Charge

Tables:

Main           Table1                  Table2       Desired result  
 ------ --      ------ ---- ------      -- ----      ------ -- ---- ------ 
|Charge|Id|    |Charge|Name|Factor|    |ID|Nom |    |Charge|ID|Name|Factor|
 ------ --      ------ ---- ------      -- ----      ------ -- ---- ------ 
| 1001 |5 |    | 1001 |ZZZ | 12   |    |5 |ZZZ |    | 1001 |5 |ZZZ | 12   |
| 1002 |8 |    | 1001 |XXX | 25   |    |5 |XXX |    | 1001 |5 |XXX | 25   |
| 1003 |10|    | 1002 |AAA | 15   |    |5 |OOO |    | 1001 |5 |OOO | 0    |
| 1006 |5 |    | 1003 |YYY | 1    |    |8 |AAA |    | 1002 |8 |AAA | 15   |
 ------ --     | 1003 |BBB | 32   |    |10|YYY |    | 1003 |10|YYY | 1    |
               | 1006 |ZZZ | 9    |    |10|UUU |    | 1003 |10|BBB | 32   |
               | 1006 |XXX | 14   |    |21|PPP |    | 1003 |10|UUU | 0    |
               | 1006 |RRR | 57   |     -- ----     | 1006 |5 |ZZZ | 9    |
               | 1011 |TTT | 11   |                 | 1006 |5 |XXX | 14   |
               | 1011 |SSS | 1    |                 | 1006 |5 |RRR | 57   |
                ------ ---- ------                   ------ -- ---- ------

CodePudding user response：

You can try to use a subquery to UNION ALL two result sets, one is made from Main join Table1 another is made from Main join Table2.

Table2 need to write a column for Factor be 0

Then use the aggregate function to get your result.

Query 1:

SELECT Charge,ID,Name,SUM(Factor) Factor
FROM (
  SELECT m.Charge,m.ID,t1.Name , t1.Factor
  FROM `Main` m
  INNER JOIN `Table1` t1
  ON m.Charge = t1.Charge
  UNION ALL
  SELECT m.Charge,t2.ID,t2.Nom , 0 Factor
  FROM `Main` m
  INNER JOIN `Table2` t2
  ON m.ID = t2.ID
) t1
GROUP BY Charge,ID,Name
ORDER BY Charge

Results:

| Charge | ID | Name | Factor |
|--------|----|------|--------|
|   1001 |  5 |  ZZZ |     12 |
|   1001 |  5 |  XXX |     25 |
|   1001 |  5 |  OOO |      0 |
|   1002 |  8 |  AAA |     15 |
|   1003 | 10 |  YYY |      1 |
|   1003 | 10 |  UUU |      0 |
|   1003 | 10 |  BBB |     32 |
|   1006 |  5 |  ZZZ |      9 |
|   1006 |  5 |  XXX |     14 |
|   1006 |  5 |  RRR |     57 |
|   1006 |  5 |  OOO |      0 |

CodePudding user response：

SELECT  Main.Charge,
        Main.Id,
        Table2.Nom,
        CASE WHEN Table2.Name = Table2.Nom
                 THEN Table1.Factor 
                 ELSE 0
        END CASE AS Factor
FROM Main
LEFT JOIN Table1
ON Main.Charge = Table1.Charge
LEFT JOIN Table2
ON Main.Id = Table2.ID

With the CASE-Statement you can select different values depending on condition you would use in a WHERE-part. Here you can use it to set your "Factor" result to the wanted value, depending on the name existing for a charge.