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
谢谢
3 个解决方案
#1
2
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)
#2
2
I am interpreting id_carrier as being the entity id for carrier:
我将id_carrier解释为承运人的实体id:
select ec.name as CustomerName, eca.name as CarrierName
from customer c join
entity ec
on c.id_entity = e.id_entity join
entity eca
on c.id_carrier = eca.id_entity
This eliminates the need to join back to the carrier table, unless you need additional information from there.
这就消除了连接返回到载波表的需要，除非您需要从那里获得其他信息。
#3
2
You need to join twice against the entity table, once for each type. Use their aliases (ca, cu below) to reference each in the SELECT list.
您需要对实体表进行两次连接，每种类型一次。使用它们的别名(ca, cu下面)引用SELECT列表中的每个别名。
SELECT
/* Carrier info */
ca.id_entity AS carrier_id_entity,
ca.name AS carrier_name,
ca.date AS carrier_date,
carrier.carrier_stuff,
/* Customer info */
cu.id_entity_as AS customer_id_entity,
cu.name AS customer_name,
cu.date AS customer_date,
customer.discount,
customer.payment
FROM
carrier
JOIN customer ON customer.carrier_id = carrier.id_entity
/* JOIN first against entity for carrier */
JOIN entity ca ON carrier.id_entity = ca.id_entity
/* and again between customer and entity */
JOIN entity cu ON customer.id_entity = cu.id_entity
#1
2
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)
#2
2
I am interpreting id_carrier as being the entity id for carrier:
我将id_carrier解释为承运人的实体id:
select ec.name as CustomerName, eca.name as CarrierName
from customer c join
entity ec
on c.id_entity = e.id_entity join
entity eca
on c.id_carrier = eca.id_entity
This eliminates the need to join back to the carrier table, unless you need additional information from there.
这就消除了连接返回到载波表的需要，除非您需要从那里获得其他信息。
#3
2
You need to join twice against the entity table, once for each type. Use their aliases (ca, cu below) to reference each in the SELECT list.
您需要对实体表进行两次连接，每种类型一次。使用它们的别名(ca, cu下面)引用SELECT列表中的每个别名。
SELECT
/* Carrier info */
ca.id_entity AS carrier_id_entity,
ca.name AS carrier_name,
ca.date AS carrier_date,
carrier.carrier_stuff,
/* Customer info */
cu.id_entity_as AS customer_id_entity,
cu.name AS customer_name,
cu.date AS customer_date,
customer.discount,
customer.payment
FROM
carrier
JOIN customer ON customer.carrier_id = carrier.id_entity
/* JOIN first against entity for carrier */
JOIN entity ca ON carrier.id_entity = ca.id_entity
/* and again between customer and entity */
JOIN entity cu ON customer.id_entity = cu.id_entity