PHP/MYSQL连接多个表 [英] PHP/MYSQL Join multiple tables

问题描述

我从来没有做过这样的PHP/MYSQL技巧来加入多表.请有此领域经验的人帮助: 表门票中的字段:

I never did such PHP/MYSQL tricks to join multitables. Please who has experience in this field Help: Fields from table 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

表付款中的字段:

RECEIPT   PAYMENT
234       cash
235       debt

表客户中的字段:

ID            NAME
9798797       John
7887878       Helen

表之间的关系非常容易理解:TICKETS.CUSTOMER=CUSTOMERS.ID; PAYMENTS.RECEIPT=RECEIPTS.ID=TICKETS.ID

The relation between tables is very easy to understand: 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");

推荐答案

您应该可以使用以下方法获取结果:

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

请参见带演示的SQL小提琴

结果:

| TICKETID |    DATENEW |  NAME | PAYMENT | TOTAL |
---------------------------------------------------
|       29 | 2012-12-03 |  John |    cash | 22.57 |
|       76 | 2012-12-03 | Helen |    debt | 33.98 |

这篇关于PHP/MYSQL连接多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！