PHP/MYSQL Join multiple tables
mysql
Solution
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
See SQL Fiddle with Demo
Result:
| TICKETID | DATENEW | NAME | PAYMENT | TOTAL |
---------------------------------------------------
| 29 | 2012-12-03 | John | cash | 22.57 |
| 76 | 2012-12-03 | Helen | debt | 33.98 |
Problem
Fields from table TICKETS: ``` ID TICKETID CUSTOMER 234 29 9798797 235 76 7887878 ``` Fields from table RECEPTS: ``` ID DATENEW TOTAL 234 2012-12-03 22.57 235 2012-12-03 33.98 ``` Fields from table PAYMENTS: ``` RECEIPT PAYMENT 234 cash 235 debt ``` Fields from table 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` 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: ``` 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 ```