Select all files and all transactions from a single table and show which transactions came from whic-CodePudding

I need help to returns all transactions and files from a single table and identify whether each transaction exists within each file? (Not all transactions exist in all files).

e.g. Table A is a list of transactions and what file they came from:

------------------------
|TransNo|FileName|Value|  (Notes)  
|T1     |File1   |50   |  (New entry)
|T2     |File1   |10   |  (New Entry)
|T1     |File2   |30   |  (Changed value)
|T3     |File2   |25   |  (New Entry)
|T3     |File3   |25   |  (unchanged)
|T4     |File3   |100  |  (New Entry)
------------------------

So from the above, you can see that:
File1 contains T1 and T2,
File2 contains T1 and T3 (assumption is that T2 is now closed),
File3 contains T3 and T4 (assumption is that T1 is also now closed)

So our 2 lists are:
Files: File1, File2, File3
Transactions: T1, T2, T3, T4

What I need is a select statement that shows all Files, along with all Transactions (not just the ones that are in each file) and whether or not the Transaction exists in that file:

--------------------------------
|FileName  |TransNo |Is Present|
| File1    | T1     | Y        |
| File1    | T2     | Y        |
| File1    | T3     | N        |
| File1    | T4     | N        |
| File2    | T1     | Y        |
| File2    | T2     | N        |
| File2    | T3     | Y        |
| File2    | T4     | N        |
| File3    | T1     | N        |
| File3    | T2     | N        |
| File3    | T3     | Y        |
| File3    | T4     | Y        |
--------------------------------

I'm currently trying to join the table back to itself, but getting too many results and can't see the way to identify whether each transaction exists in each file yet.

Any pointers would be greatly appreciated, because I've been going around in circles on this one, and I just need to be done with it.

Thanks

-- Solution --

Based on Sanchez333's suggestion (bearing in mind that he replied before I altered the request to joining a single table back to itself), this is what I'm using:

SELECT
 T1.Filename
,T1.TransNo
,CASE WHEN T2.Value IS NULL THEN 'N'
      ELSE 'Y'
  END AS Is_Present

FROM 
  ( --this subquery gets the full list of filenames and transactions
    SELECT DISTINCT
    st1.Filename
    ,st2.TransNo

    FROM
    tableA st1
    FULL JOIN tableA st2
    ON st1.{other columns} = st2.{other columns}
  ) AS T1
LEFT OUTER JOIN --now we join to check if there's a value
tableA AS T2
ON T1.Filename = T2.Filename
    AND T1.TransNo = T2.TransNo

CodePudding user response：

I think you want to use a FULL JOIN here.

This might need some work but I hope it points you in the right direction:

SELECT 
T1.Filename
,CASE WHEN T2.Value IS NULL THEN 'N'
      ELSE 'Y'
  END AS Is_Present

FROM 
  ( --this subquery gets the full list of filenames and transactions
    SELECT DISTINCT
    st1.Filename
    ,st2.TransNo

    FROM
    table1 st1
    FULL JOIN table2 st2
  ) AS T1
LEFT OUTER JOIN --now we join to check if there's a value
table2 AS T2
ON T1.Filename = T2.Filename
    AND T1.TransNo = T2.TransNo