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

Tags:

Mysql