CODEWITHSUNDEEP
mysqlsqljoinleft-join
Happy Coder
Happy Coder

Reputation: 4682

Distinct value when using left join with two select statements

I have two tables as follows:

migration_log
-------------
migration_log_id
release_id
environment_id,
requestor,
status_datetime
status

log_details
----------------------
migration_log_detail_id
migration_log_id
release_id
build_item
artifact_type
artifact_file_name
version_identifier
status,
status_description
remarks

and the tables has data as shown in the fiddle here: http://sqlfiddle.com/#!9/b4b65c/1

Now I need to select the files migrated to one source environment and not migrated to the target environment. The result will have following columns:

artifact_file_name | artifact_type | source_version | target_version

If the file exists in source and not on target, the target version will be shown as 'NA', otherwise, the version of the file present in target. I am using the following query for finding the results.

SELECT DISTINCT
   src.artifact_file_name,
   src.version_identifier as source_version,
   src.artifact_type,
   IFNULL(tgt.version_identifier, 'NA') as target_version 
FROM
   (
      SELECT DISTINCT
         artifact_file_name,
         version_identifier,
         artifact_type 
      FROM
         migration_log_detail 
         JOIN
            migration_log 
            ON migration_log_detail.migration_log_id = migration_log.migration_log_id 
      WHERE
         environment_id = 'SOURCE_NVIRONMENT_ID' 
         AND migration_log_detail.status = 'SUCCESS' 
   )
   src 
   JOIN
      (
         SELECT DISTINCT
            artifact_file_name,
            version_identifier 
         FROM
            migration_log_detail 
            JOIN
               migration_log 
               ON migration_log_detail.migration_log_id = migration_log.migration_log_id 
         WHERE
            environment_id = 'TARGET_ENVIRONMENT_ID' 
            AND migration_log_detail.status = 'SUCCESS' 
      )
      tgt 
      ON src.artifact_file_name = tgt.artifact_file_name

Now when I use this query with source as 'TEST_22' and target as 'BVT' as per the data given in the fiddle, I am getting the target version repeated. The same file name is repeated for two environments, but for different types. So for one entry it should show NA and for the other one it should be the version. I have added the current query to the fiddle and it would be very much helpful if someone can help me.

Please find the table data below:

Table - migration_log

| migration_log_id | release_id | environment_id | requested_by |      status_datetime |   status |
|------------------|------------|----------------|--------------|----------------------|----------|
|                2 |         74 |            BVT |    DEVELOPER | 2020-07-22T00:00:00Z | COMPLETE |
|               47 |         94 |        TEST_22 |        ADMIN | 2020-08-21T11:13:11Z |  SUCCESS |
|               48 |         94 |        TEST_22 |        ADMIN | 2020-08-21T11:14:19Z |  SUCCESS |
|               49 |         94 |        TEST_22 |    DEVELOPER | 2020-08-21T11:14:19Z |  SUCCESS |
|               50 |         94 |        TEST_22 |        ADMIN | 2020-08-21T11:14:20Z |  SUCCESS |
|               55 |         84 |        TEST_22 |        ADMIN | 2020-10-06T05:47:27Z |  FAILURE |
|               56 |         85 |            BVT |    DEVELOPER | 2020-10-06T05:47:27Z |  SUCCESS |
|               57 |         84 |        TEST_22 |      RMADMIN | 2020-10-23T11:50:21Z |  FAILURE |
|               58 |         85 |            BVT |      RMADMIN | 2020-10-23T11:50:21Z |  FAILURE |

Table - migration_log_detail

| migration_log_detail_id | migration_log_id | release_id | build_item |        artifact_type |   artifact_file_name | version_identifier |  status | status_desc |             remarks |
|-------------------------|------------------|------------|------------|----------------------|----------------------|--------------------|---------|-------------|---------------------|
|                       3 |                2 |         74 |      ALERT | 001-DEVELOPERWEBSITE |              one.xml |                228 | SUCCESS |        INFO |  Artifact migration |
|                       4 |                2 |         74 |      ALERT | 001-DEVELOPERWEBSITE |              two.xml |                228 | SUCCESS |             |              (null) |
|                       5 |                2 |         74 |      ALERT | 001-DEVELOPERWEBSITE |     three_domain.xml |                228 | SUCCESS |      (null) |              (null) |
|                       6 |                2 |         74 |      ALERT | 001-DEVELOPERWEBSITE |        four_type.xml |                228 | SUCCESS |      (null) |              (null) |
|                       7 |                2 |         74 |      ALERT | 001-DEVELOPERWEBSITE |      five_domain.xml |                228 | SUCCESS |        INFO |  Artifact migration |
|                       8 |                2 |         74 |      ALERT | 001-DEVELOPERWEBSITE |       six_domain.xml |                228 | SUCCESS |      (null) |              (null) |
|                       9 |                2 |         74 |      ALERT | 001-DEVELOPERWEBSITE |            seven.xml |                228 | SUCCESS |      (null) |              (null) |
|                      10 |                2 |         74 |      ALERT | 001-DEVELOPERWEBSITE |            eight.xml |                228 | SUCCESS |      (null) |              (null) |
|                      11 |                2 |         74 |      ALERT |  067-DEVELOPERCONFIG |             nine.xml |                230 | SUCCESS |      (null) |              (null) |
|                      12 |                2 |         74 |      ALERT |    027-DEVELOPERAPPS |              ten.xml |                233 | SUCCESS |      (null) |              (null) |
|                      13 |                2 |         74 |      ALERT |  004-DEVELOPERSCRIPT |           eleven.xml |                234 | SUCCESS |      (null) |              (null) |
|                      14 |                2 |         74 |      ALERT |  004-DEVELOPERSCRIPT |        file_name.xml |                234 | SUCCESS |      (null) |              (null) |
|                      15 |                2 |         74 |      ALERT |  004-DEVELOPERSCRIPT |         thirteen.xml |                234 | SUCCESS |      (null) |              (null) |
|                      16 |                2 |         74 |      ALERT | 021-DEVELOPERSUPPORT |          another.xml |                220 | FAILURE |     WARNING | Newer Version Found |
|                      17 |                2 |         74 |      ALERT | 021-DEVELOPERSUPPORT |             test.xml |                220 | SUCCESS |      (null) |              (null) |
|                      18 |                2 |         74 |      ALERT | 021-DEVELOPERSUPPORT |        test_file.xml |                220 | SUCCESS |      (null) |              (null) |
|                      42 |               48 |         94 |     UE-762 |            GOOG_CODE |             name.xml |              13277 | SUCCESS |        INFO |                NULL |
|                      43 |               49 |         94 |     UE-763 |           GOOG_POINT |      launch_file.xml |              13277 | SUCCESS |        INFO |                NULL |
|                      44 |               50 |         94 |     UE-764 |           GOOG_POINT |          warning.xml |              13277 | SUCCESS |        INFO |                NULL |
|                      51 |               55 |         84 |     UE-762 |            GOOG_CODE |          success.xml |              13277 | SUCCESS |        INFO |                NULL |
|                      52 |               55 |         84 |     UE-762 |           GOOG_POINT |      success_teo.xml |              13277 | FAILURE |        INFO |                NULL |
|                      53 |               56 |         85 |     UE-254 |             GOOG_SQL |             dupe.xml |              13271 | SUCCESS |        INFO |                NULL |
|                      54 |               56 |         85 |     UE-022 |             GOOG_SQL |        work_load.xml |              13271 | SUCCESS |        INFO |                NULL |
|                      55 |               56 |         85 |     UE-022 |             GOOG_SQL |       work_load2.xml |              13271 | SUCCESS |        INFO |                NULL |
|                      56 |               56 |         85 |     UE-345 |             GOOG_URL |         location.xml |              13214 | FAILURE |        INFO |                NULL |
|                      57 |               57 |         84 |     UE-762 |            GOOG_CODE |  warning_message.xml |              13277 | SUCCESS |        INFO |                NULL |
|                      58 |               57 |         84 |     UE-762 |           GOOG_POINT | warning_message2.xml |              13277 | FAILURE |        INFO |                NULL |
|                      59 |               58 |         85 |     UE-254 |             GOOG_SQL |            dupe2.xml |              13271 | SUCCESS |        INFO |                NULL |
|                      60 |               58 |         85 |     UE-022 |             GOOG_SQL |            order.xml |              13271 | SUCCESS |        INFO |                NULL |
|                      61 |               58 |         85 |     UE-022 |             GOOG_SQL |           order2.xml |              13271 | SUCCESS |        INFO |                NULL |
|                      62 |               58 |         85 |     UE-345 |           GOOG_POINT |      launch_file.xml |              13214 | SUCCESS |        INFO |                NULL |
|                      90 |               49 |         94 |     UE-763 |            GOOG_FILE |      launch_file.xml |              13277 | SUCCESS |        INFO |                NULL |

Now the current result for source TEST_22 and target BVT as is as follows:

| artifact_file_name | source_version | artifact_type | target_version |
|--------------------|----------------|---------------|----------------|
|    launch_file.xml |          13277 |    GOOG_POINT |          13214 |
|    launch_file.xml |          13277 |     GOOG_FILE |          13214 |

The problem here is that the target_version is repeating here. It should show NA for GOOG_FILE and 13214 for GOOG_POINT.

Upvotes: 1

Views: 153

Answers (1)

Prasanna
Prasanna

Reputation: 2488

Instead of ifnull, you can use CASE statement to acheive the result - http://sqlfiddle.com/#!9/b4b65c/44

Query Modifications:

  • introduced artifact_type in tgt subquery.
  • Only populate target_version when the artifact types match else set it as NA using the expression - case when src.artifact_type = tgt.artifact_type then tgt.version_identifier else 'NA'

Complete Query:

SELECT DISTINCT src.artifact_file_name, 
                src.version_identifier AS source_version, 
                src.artifact_type, 
                CASE 
                  WHEN src.artifact_type = tgt.artifact_type THEN 
                  tgt.version_identifier 
                  ELSE 'NA' 
                END                    AS target_version 
FROM   (SELECT DISTINCT artifact_file_name, 
                        version_identifier, 
                        artifact_type 
        FROM   migration_log_detail 
               JOIN migration_log 
                 ON migration_log_detail.migration_log_id = 
                    migration_log.migration_log_id 
        WHERE  environment_id = 'TEST_22' 
               AND migration_log_detail.status = 'SUCCESS') src 
       JOIN (SELECT DISTINCT artifact_file_name, 
                             version_identifier, 
                             artifact_type 
             FROM   migration_log_detail 
                    JOIN migration_log 
                      ON migration_log_detail.migration_log_id = 
                         migration_log.migration_log_id 
             WHERE  environment_id = 'BVT' 
                    AND migration_log_detail.status = 'SUCCESS') tgt 
         ON src.artifact_file_name = tgt.artifact_file_name

Upvotes: 1

Related Questions