PHP/MYSQL Join multiple tables

Question

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

see stackoverflow.com/questions/1204217/mysql-select-join-3-tables — Mayank K. Swami
– Mayank K. Swami, Commented Dec 3, 2012 at 13:58
Why you are reference the table People in the from clause where you are not selecting anything from it, nor you sepecified the join condition of it with the other tables? — Mahmoud Gamal
– Mahmoud Gamal, Commented Dec 3, 2012 at 14:02

Taryn · Accepted Answer · 2012-12-03 14:05:57Z

10

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 |

answered Dec 3, 2012 at 14:05

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sergiu Costas Over a year ago

Thank you! your way was first and I modified some small mistakes there like p.total instead of r.total by myself. I am surprised your code is working in Demo :)

MrCode · Accepted Answer · 2012-12-03 14:05:00Z

5

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

answered Dec 3, 2012 at 14:05

MrCode

64.6k10 gold badges95 silver badges114 bronze badges

1 Comment

Sergiu Costas Over a year ago

Thank you very much for you code! It is a good one as well, sorry but I do not know how to choose two answers :) Really appreciate your effort!

Collectives™ on Stack Overflow

PHP/MYSQL Join multiple tables

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related