Start with building 2 separate user views:
A user view on the CA 1 Volumes (VISTMCS) object with the required filter definition, for example -
"Ext Data Manager" = Y AND "Data Mgr ID" = DMS AND "Scratch" = Y AND "Delete" = N AND "Volume" INCL ABC/
And another user view on the CA Disk Archvols Records (DMSARCHV) object with a corresponding filter, for example -
"Arc Key" INCL ABC/
Do this just to learn what data you have and what you would expect as output list when relating both in a Joined Object.
Then create the Joined Object via the wizard.
To list the CA1 tapes that have become scratch while there is still a corresponding archive data set,
you need to find matching records where the CA1 Volume is equal to the ARCKEY.
This is done by the Inner Join.
You will find more information on the join types in the GUI wizard help text when you create the join,
or in the Glossary of the Reference Guide, for example:
Inner Join is a type of join applied for the join in CA Vantage SRM Joined Objects. An inner join represents the default join-type.
Inner join creates a Joined Object by combining field values of two objects (Base=A and Candidate=B) based upon the join-operator.
The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate.
When the join-predicate is satisfied, field values for each matched pair of rows of A and B are combined into a result row.