ORACLE – Using left join and inner join to edit a query

I am studying this query and I am trying, but I am not able to return all ships regardless of the sector of the user and the sector of the ship. (line 5)

The query as it is, returns all ships that are no longer associated with a user as long as the sector of the ship is the same sector as the user.

I would like all ships that are not associated with a user to return, regardless of the user’s and ship sector

I tried to remove the innerJoin, to avoid the comparison, but it’s giving an error

There are 3 tables: Users, Ships and Ships_Users

            select 
            sh.Id,
            sh.Name
            from USERS  user                 
            inner join Ship sh on ltrim(rtrim(sh.Sector)) = ltrim(rtrim(user.Setor))
            LEFT JOIN SHIP_USER su on su.ship_id = sh.id and su.user_id = user.id
            where user.id =:userId
            and su.id is null and sh.Active = 1;

Answer

I would like all ships that are not associated with a user to return, regardless of the user’s and ship sector

You seem to be asking for this:

select s.*
from ship s
where not exists (select 1
                  from ship_users su
                  where su.ship_id = s.id and su.user_id = :user_id
                 ) and 
      s.isactive = 1;