mysql - mysql中不同表id的相同列名

Question

表格

person_id   (primary key)

phs_people  (person_id,first_name,last_name)

phs_cutomers (person_id,company_name) 

phs_waiters (person_id,commission)

person_id 是他们之间的关键。

所以我的问题如何通过 person_id 检索顾客的名字和姓氏，服务员的名字和姓氏？

SELECT 
c.first_name AS customer_Fist_name,
c.last_name AS Customer_LastName, 
c.first_name AS WaiterFirstName, 
c.last_name AS Waiter_LastName,
invoice_number, amount_tendered, sale_time, DATE_FORMAT( sale_time, '%d-%m-%Y' ) AS sale_date, phs_sales.sale_id AS sale_id, SUM( item_unit_price * quantity_purchased * ( 1 - discount_percent /100 ) ) AS amount_due
FROM (
phs_sales
)
LEFT JOIN phs_people c ON c.person_id = phs_sales.customer_id
AND person_id = phs_sales.waiter_id
JOIN phs_sales_items ON phs_sales_items.sale_id = phs_sales.sale_id
LEFT JOIN (

SELECT sale_id, SUM( payment_amount ) AS amount_tendered
FROM phs_sales_payments
WHERE payment_type <> 'Check'
GROUP BY sale_id
) AS payments ON payments.sale_id = phs_sales.sale_id
GROUP BY sale_id
ORDER BY sale_time DESC
LIMIT 25

如果我执行此查询，则会收到以下错误:

customer_Fist_name NULL,Customer_LastName NULL, WaiterFirstName NULL, Waiter_LastName NULL,

Answer 1

您想要在同一张表上执行两次 JOIN，但使用不同的值(顾客的数据和服务员的数据)，但您只需使用一次 JOIN 即可给出这两个条件。

要解决此问题，您必须加入 phs_people-Table 两次，如下所示:

...
LEFT JOIN phs_people AS c1 ON c1.person_id = phs_sales.customer_id
LEFT JOIN phs_people AS c2 ON c2.person_id = phs_sales.waiter_id
...

然后选择正确的数据，如下所示:

SELECT
c1.first_name AS customer_Fist_name,
c1.last_name AS Customer_LastName,
c2.first_name AS WaiterFirstName,
c2.last_name AS Waiter_LastName,
...

PS:通过此查询，您仍然应该获得多个 NULL-值，这是因为您的 phs_sales-Table 的一半填充了空字段...