PHP/MYSQL 加入多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13684796/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PHP/MYSQL Join multiple tables
提问by Sergiu Costas
I never did such PHP/MYSQL tricks to join multitables. Please who has experience in this field Help: Fields from table TICKETS:
我从来没有做过这样的 PHP/MYSQL 技巧来加入多表。请有这方面经验的人帮助：TICKETS表中的字段：
ID TICKETID CUSTOMER
234 29 9798797
235 76 7887878
Fields from table RECEPTS:
表RECEPTS 中的字段：
ID DATENEW TOTAL
234 2012-12-03 22.57
235 2012-12-03 33.98
Fields from table PAYMENTS:
表PAYMENTS 中的字段：
RECEIPT PAYMENT
234 cash
235 debt
Fields from table CUSTOMERS:
表CUSTOMERS 中的字段：
ID NAME
9798797 John
7887878 Helen
The relation between tables is very easy to understand: TICKETS.CUSTOMER=CUSTOMERS.ID;PAYMENTS.RECEIPT=RECEIPTS.ID=TICKETS.ID
表之间的关系很容易理解： TICKETS.CUSTOMER=CUSTOMERS.ID;PAYMENTS.RECEIPT=RECEIPTS.ID=TICKETS.ID
The Final Result I would like to achive to have:
我想达到的最终结果：
TICKETID DATENEW NAME PAYMENT TOTAL
29 2012-12-03 John cash 22.57
76 2012-12-03 Helen debt 33.98
I tried to do something like this but it wrong somewhere:
我试图做这样的事情，但在某处错了：
$qry = mysql_query("Select TICKETS.TICKETID, RECEIPTS.DATENEW, PAYMENTS.TOTAL, CUSTOMERS.NAME, PAYMENTS.PAYMENT FROM PEOPLE, RECEIPTS
INNER JOIN TICKETS ON RECEIPTS.ID = TICKETS.ID
INNER JOIN CUSTOMERS ON TICKETS.CUSTOMER = CUSTOMERS.ID
ORDER BY RECEIPTS.DATENEW");
回答by Taryn
You should be able to use the following to get the result:
您应该能够使用以下内容来获得结果：
select t.ticketid,
date_format(r.datenew, '%Y-%m-%d') datenew,
c.name,
p.payment,
r.total
from tickets t
left join RECEPTS r
on t.id = r.id
left join CUSTOMERS c
on t.customer = c.id
left join payments p
on t.id = p.RECEIPT
and r.id = p.RECEIPT
Result:
结果：
| TICKETID | DATENEW | NAME | PAYMENT | TOTAL |
---------------------------------------------------
| 29 | 2012-12-03 | John | cash | 22.57 |
| 76 | 2012-12-03 | Helen | debt | 33.98 |
回答by MrCode
This will give the output that you want:
这将提供您想要的输出：
SELECT
p.RECEIPT AS TICKETID,
r.DATENEW,
c.NAME,
p.PAYMENT,
r.TOTAL
FROM
PAYMENTS p
LEFT JOIN
RECEIPTS r ON r.ID = p.RECEIPT
LEFT JOIN
TICKETS t ON t.ID = p.RECEIPT
LEFT JOIN
CUSTOMERS c ON c.ID = t.CUSTOMER
ORDER BY
r.DATENEW DESC
