SQL insert null value when join delivers no result-CodePudding

I have three tables

articleoffer_test_u
   id | randomId    | articleOfferId | TestBegin  | ArticleId
   1  | 12345678    | 38             | 2021-12-08 | 74        
   2  | 12345679    | 1              | 2021-12-08 | 1        
   3  | 12345677    | 1              | 2021-12-08 | 1        

articleoffer_feature_test_u
   ArticleOfferTestId | FeatureId     
   1                  | EXAMPLE1       
   1                  | EXAMPLE2             
   1                  | EXAMPLE3     
   2                  | EXAMPLE1        
   2                  | EXAMPLE3              
   3                  | EXAMPLE3        
   3                  | EXAMPLE2        

featurealias
   Id | FeatureId      | ArticleShortName | FeatureAlias
   1  | EXAMPLE1       | ART1             | EXAMPLE1_E 
   2  | EXAMPLE1       | ART2             | EXAMPLE1_J
   2  | EXAMPLE2       | ART1             | EXAMPLE2_E

I use this query

SELECT amtu.FeatureId, ptu.testBegin
    FROM articleoffer_test_u ptu, articleoffer_feature_test_u amtu
    WHERE ptu.RandomId="12345678" AND ptu.ArticleId=74 AND ptu.articleOfferId=38
    AND ptu.id=amtu.ArticleofferTestId AND amtu.FeatureId IN ("EXAMPLE1", "EXAMPLE2", "EXAMPLE3")

and get this result

 FeatureId      | TestBegin
 EXAMPLE1       | 2021-12-08 
 EXAMPLE2       | 2021-12-08
 EXAMPLE3       | 2021-12-08

what I want is a result like this

 FeatureId      | FeatureAlias   | TestBegin
 EXAMPLE1       | EXAMPLE1_E     | 2021-12-08
 EXAMPLE2       | EXAMPLE2_E     | 2021-12-08
 EXAMPLE3       | NULL           | 2021-12-08

I tried this query

SELECT amtu.FeatureId, ptu.testBegin, featureAlias.FeatureAlias
    FROM articleoffer_test_u ptu, articleoffer_feature_test_u amtu
    INNER JOIN featureAlias ON featureAlias.FeatureId = amtu.FeatureId AND featureAlias.ArticleShortName = "ART1"
    WHERE ptu.RandomId="12345678" AND ptu.ArticleId=74 AND ptu.articleOfferId=38
    AND ptu.id=amtu.ArticleofferTestId AND amtu.FeatureId IN ("EXAMPLE1", "EXAMPLE2", "EXAMPLE3");

and get this result where EXAMPLE3 is missing

 FeatureId      | FeatureAlias   | TestBegin
 EXAMPLE1       | EXAMPLE1_E     | 2021-12-08
 EXAMPLE2       | EXAMPLE2_E     | 2021-12-08

The reason for this is that there is no featureAlias for EXAMPLE3. Is there a possibility to use a join and insert null for the featureAlias value? If no, does anyone know a different approach for solving the problem?

CodePudding user response：

Left Join does that automatically:

SELECT amtu.FeatureId,
       ptu.testBegin,
       featureAlias.FeatureAlias
FROM   articleoffer_test_u ptu
       INNER JOIN articleoffer_feature_test_u amtu
               ON ptu.id = amtu.ArticleofferTestId
       LEFT JOIN featureAlias
              ON featureAlias.FeatureId = amtu.FeatureId
                 AND featureAlias.ArticleShortName = 'EXMP1'
WHERE  ptu.RandomId = '12345678'
       AND ptu.ArticleId = 74
       AND ptu.articleOfferId = 38
       AND amtu.FeatureId IN ( 'EXAMPLE1', 'EXAMPLE2', 'EXAMPLE3' );