Get all possible entries SQL Join

A simple join can't produce this result. You need to walk a transition graph to generate each query. You can do this with a recursive query using a CTE

Given these tables :

declare @product table ( Category nvarchar(30), Year int, ProductCode nvarchar(30))

insert into @product 
values
('Animals',1998 ,'A0001'),
('Sports'   ,2001,'A0002');

declare @substitusion table (ProductFrom nvarchar(30),ProductTo nvarchar(30));

insert into @substitusion 
values
('A0001','A0003'),
('A0002','A0004'),
('A0003','A0005'),
('A0004','A0006'),
('A0006','A0007');

This query will walk the transitions and generate the desired result :

with x as (
    --Create the initial result by joining Product and Substitution
    select Category,Year,ProductFrom,ProductTo
    from @product p inner join @substitusion s on p.ProductCode=s.ProductFrom
    union all
    --Join the *previous* result with the next Substitution
    select Category,Year,next.ProductFrom,next.ProductTo
    from x as previous 
    inner join @substitusion next on previous.ProductTo=next.ProductFrom
)
select * 
from x
order by Category

This produces :

Category    Year    ProductFrom ProductTo
Animals     1998    A0001       A0003
Animals     1998    A0003       A0005
Sports      2001    A0002       A0004
Sports      2001    A0004       A0006
Sports      2001    A0006       A0007

The first query generates the first results by joining Product and Substitution. The next query joins any previous results with the next substitution, by joining the previous ProductTo to the next ProductFrom