Help with the query

Question

Combination of the fields from the 2 tables below:

Master
------
A_ID (PK)
SYSSerialNumber
logtime
FileName


SYSTEM (Master:SYSTEM -1:1)
------
SYS_ID
A_ID (FK)
Version

Combined Result:

SerialNumber    logtime    Filename            Version

CZJ             23         ABC.txt             1.02
CZJ             234        ABC.txt             1.02
CZJ             123        ABC.txt             1.1
CZJ             2343       CDE.txt             1.05
CZJ             24343      CDE.txt             1.05
RXZ             343        HP.txt              1.1
RXZ             4232       HP.txt              1.1
RXZ             2424       HP_1.txt            1.1
RXZ             2424       HP_1.txt            1.1
XYZ             3345       MNO.txt             1.05
XYZ             34634      MNO.txt             1.1
XYZ             45         MNO.txt             1.03
XYZ             3463       MNO.txt             1.02

My Required result: File names that have ONLY 1.1 version and no other

The filenames displayed would be HP.txt and HP_1.txt

select  A.SerialNumber, A.logtime, Version,
A.AHS_FILENAME
from    MASTER A,  SYSTEM B, 
        (SELECT dd.A_ID, FILENAME,  COUNT(DISTINCT
EE.Version)  version_count
         FROM MASTER DD, SYSTEM EE
         WHERE DD.A_ID =  EE.A_ID 
         GROUP BY dd.A_ID, dd.A_FILENAME) C
where   A.A_ID =  B.A_ID 
and     A.A_ID =  C.A_ID
and     A.FILENAME = C.FILENAME
and     B.Version='1.1'
and     C.version_count = 1

But I get the filenames with other versions included along with 1.1

Would appreciate any help.

Answer 1

In my other answer, I've shown you how you could fix your present query to solve the problem. Now I'd like to suggest a different solution.

First of all, my assumption is that the Combined Result you posted in your question can be obtained using this simple join:

SELECT
  M.SerialNumber,
  M.logtime,
  M.Filename,
  S.Version
FROM MASTER M
INNER JOIN SYSTEM S
  ON M.A_ID = S.A_ID
;

At this point, you could use window aggregate functions MIN() OVER ... and MAX() OVER ... to check if a file has more than one version: if the results of the two functions match, then there's just one version, otherwise there are two or more:

SELECT
  M.SerialNumber,
  M.logtime,
  M.Filename,
  S.Version,
  IsSingleVersion = CASE MIN(S.Version) OVER (PARTITION BY M.Filename)
                    WHEN MAX(S.Version) OVER (PARTITION BY M.Filename)
                    THEN 1
                    ELSE 0
                    END
FROM MASTER M
INNER JOIN SYSTEM S
  ON M.A_ID = S.A_ID
;

This query produces the following results:

SerialNumber    logtime    Filename     Version    IsSingleVersion

CZJ             23         ABC.txt      1.02       0
CZJ             234        ABC.txt      1.02       0
CZJ             123        ABC.txt      1.1        0
CZJ             2343       CDE.txt      1.05       1
CZJ             24343      CDE.txt      1.05       1
RXZ             343        HP.txt       1.1        1
RXZ             4232       HP.txt       1.1        1
RXZ             2424       HP_1.txt     1.1        1
RXZ             2424       HP_1.txt     1.1        1
XYZ             3345       MNO.txt      1.05       0
XYZ             34634      MNO.txt      1.1        0
XYZ             45         MNO.txt      1.03       0
XYZ             3463       MNO.txt      1.02       0

Now, as you can see, the query merely returns the flag indicating if a file has a single version or not. You, however, probably want to filter on that flag. To filter on it, use the above query as a derived table, e.g. as a CTE:

WITH checked AS (
   SELECT
     M.SerialNumber,
     M.logtime,
     M.Filename,
     S.Version,
     IsSingleVersion = CASE MIN(S.Version) OVER (PARTITION BY M.Filename)
                       WHEN MAX(S.Version) OVER (PARTITION BY M.Filename)
                       THEN 1
                       ELSE 0
                       END
   FROM MASTER M
   INNER JOIN SYSTEM S
     ON M.A_ID = S.A_ID
)
SELECT
  SerialNumber,
  logtime,
  Filename,
  Version,
FROM checked
WHERE IsSingleVersion = 1
  AND Version = '1.1'
;

Of course, it would probably be better to use the window functions in the WHERE clause directly, but that is restricted by the syntax: window functions are only allowed in SELECT and ORDER BY.

Answer 2

I assume that this bit in your question

Master:SYSTEM -1:1

means that the Master table has a 1:1 relationship with the SYSTEM table. Consequently, since A_ID is the primary key of Master, the same column should be unique in SYSTEM too, otherwise the 1:1 relationship would be violated.

If the above is correct, the problem in your query is that you are using A_ID in as a grouping criterion. You are essentially calculating distinct version counts per FILENAME and A_ID, while you should likely be calculating them per FILENAME only.

So, the simplest fix to your query would probably be to remove A_ID from the subquery (specifically, from SELECT and GROUP BY) and the corresponding condition from the main query's WHERE:

select  A.SerialNumber, A.logtime, Version,
A.AHS_FILENAME
from    MASTER A,  SYSTEM B, 
        (SELECT dd.A_ID, FILENAME,  COUNT(DISTINCT
EE.Version)  version_count
         FROM MASTER DD, SYSTEM EE
         WHERE DD.A_ID =  EE.A_ID 
         GROUP BY dd.A_ID, dd.A_FILENAME) C
where   A.A_ID =  B.A_ID 
and     A.A_ID =  C.A_ID
and     A.FILENAME = C.FILENAME
and     B.Version='1.1'
and     C.version_count = 1

However, I would also suggest you consider using implicit joins in your queries. And so, I would probably re-write the above like this (minus the formatting, which is a matter of taste and, therefore, up to you):

SELECT
  A.SerialNumber,
  A.logtime,
  B.Version,
  A.AHS_FILENAME
FROM MASTER A
INNER JOIN SYSTEM B
  ON A.A_ID =  B.A_ID
INNER JOIN (
  SELECT DD.FILENAME, COUNT(DISTINCT EE.Version) version_count
  FROM MASTER DD
  INNER JOIN SYSTEM EE
    ON DD.A_ID =  EE.A_ID 
  GROUP BY DD.A_FILENAME
) C
  ON A.FILENAME = C.FILENAME
WHERE B.Version='1.1'
  AND C.version_count = 1
;

This way it is easier to distinguish strictly joining conditions from filtering ones.