In Oracle SQL is there a way to join on a value twice?

Lets say I have two tables, with the following columns:

cars

motorcycle_id | fuel_id | secondary_fuel_id ...  

fuel_types

fuel_id | fuel_label | ...

In this case fuel_id and secondary fuel_id both refer to the fuel_types table.

Is it possible to include both labels in an inner join? I want to join on the fuel_id but I want to be able to have the fuel label twice as a new column. So the join would be something like:

motorcycle_id | fuel_id | fuel_label | secondary_fuel_id | secondary_fuel_label | ...

In this case I would have created the secondary_fuel_label column.

Is this possible to do in SQL with joins? Is there another way to accomplish this?

Answer

You would just join twice:

select c.*, f1.fuel_label, f2.fuel_label as secondary_fuel_label
from cars c left join
     fuel_types f1
     on c.fuel_id = f1.fuel_id left join
     fuel_types f2
     on c.fuel_id = f1.secondary_fuel_id ;

The key point here is to use table aliases, so you can distinguish between the two table references to fuel_types.

Note that this uses left join to be sure that rows are returned even if one of the ids are missing.