SQL - Select the same column, twice

Question

I don't know if it is correct, but look the following schema:

An Entity can be either a Carrier or a Customer. Every customer must be associated with a Carrier, this Carrier can be the own Customer.

I'm stuck, tried without success to perform a single query that returns the NAME of the customer and the NAME of his Carrier. Is there a way to make such operation ?

Thanks

Answer 1

Join entity twice and give the name column aliases. Remove the last join if additional carrier fields are not needed.

select customer_entity.name as customer_name, customer.credit, -- additional customer fields...
        carrier_entity.name as carrier_name, carrier.carrier_stuff -- additional carrier fields...
        from entity as customer_entity
        inner join customer on (customer.id_entity = customer_entity.id_entity)
        inner join entity as carrier_entity on (customer.id_carrier = carrier_entity.id_entity)
        inner join carrier on (carrier.id_entity = carrier_entity.id_entity)