我从来没有这样的
PHP / MysqL技巧来加入multitables.请谁有此领域的经验帮助：
表TICKETS中的字段：
表TICKETS中的字段：
- ID TICKETID CUSTOMER
- 234 29 9798797
- 235 76 7887878
表RECEPTS中的字段：
- ID DATENEW TOTAL
- 234 2012-12-03 22.57
- 235 2012-12-03 33.98
表PAYMENTS中的字段：
- RECEIPT PAYMENT
- 234 cash
- 235 debt
表CUSTOMERS中的字段：
- ID NAME
- 9798797 John
- 7887878 Helen
表之间的关系很容易理解：TICKETS.CUSTOMER = CUSTOMERS.ID; PAYMENTS.RECEIPT = RECEIPTS.ID = TICKETS.ID
最后的结果我想达成：
- TICKETID DATENEW NAME PAYMENT TOTAL
- 29 2012-12-03 John cash 22.57
- 76 2012-12-03 Helen debt 33.98
我试图做这样的事情,但在某处错了：
- $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");
您应该能够使用以下内容来获得结果：
- 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
结果：
- | TICKETID | DATENEW | NAME | PAYMENT | TOTAL |
- ---------------------------------------------------
- | 29 | 2012-12-03 | John | cash | 22.57 |
- | 76 | 2012-12-03 | Helen | debt | 33.98 |
