PHP/MYSQL Join multiple tables
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 |
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