SQL to select items based on multiple conditions in same table with concatenated result

Question

I have the following scenario:

I am storing storing migration related information in two tables :

log
-------------
log_id
release_id
environment_id
status

log_details
----------------------
log_detail_id
log_id
release_id
item_name
type
file_name
version_number
status
remarks

Now I need to select the file name which are present in one environment and not there in another environment. I am using a query like the following:

SELECT file_name, version_number from log_details JOIN log ON log_details.log_id = log.log_id 
WHERE environment_id = 'SOURCE_ENVIRONMENT_ID' AND log_details.status='SUCCESS' 
AND file_name NOT IN (
    SELECT DISTINCT file_name from log_details JOIN log ON log_details.log_id = log.log_id 
    WHERE environment_id = 'TARGET_ENVIRONMENT_ID' AND log_details.status='SUCCESS'
);

This is working fine and showing the file name and corresponding version number. Now as per new requirement, I need to compare the versions of the files also. That is it should show the file names even if the file name exists in both the environments , but with different versions.

So the select should show 3 columns like file_name, source_version, target_version. If the file doesn't exists on target, it should show NA. How can I do this ?

Update : Adding fiddle:

http://sqlfiddle.com/#!9/6b28d7/1

Answer 1

This may be updated later to reflect any sample data you provide, but this sounds like it suits your purpose:

SELECT 
  src.file_name,
  src.version_number as source_version,
  IFNULL(tgt.version_number, 'NA') as target_version
FROM (
  SELECT 
      log_details.file_name,
      log_details.version_number
  FROM log_details
  JOIN logs ON log_details.log_id = logs.log_id
  WHERE logs.environment_id = 'DEV'
    AND logs.STATUS = 'SUCCESS'
  ) src
LEFT JOIN (
  SELECT 
      log_details.file_name,
      log_details.version_number
  FROM log_details 
  JOIN logs ON log_details.log_id = logs.log_id 
  WHERE logs.environment_id = 'PROD'
    AND logs.status='SUCCESS' 
) tgt ON src.file_name = tgt.file_name 

As a bonus, you can still get the results of your original query with WHERE tgt.version_number IS NULL.

EDIT:

With your fiddle data, and assuming DEV is your "source" environment, this results in the output:

+-----------------------------------------------+
|file_name   |source_version    | target_version|
+-----------------------------------------------+
|file_name_1 |     100          |      101      |
|file_name_2 |     100          |      NA       |
|file_name_3 |     100          |      NA       |
+-------------------------------+---------------+

This doesn't seem very useful, and I imagine if you provide a clear "expected results" layout, what you want is not the same as what you asked for, but I'm only guessing.