I have some problem in fetching data in MySQL.
I have three tables - Invoice
, Invoice_detail
and Invoice_ticket_detail
.
Invoice_detail
and invoice_ticket_detail
both have data of invoice I want to get all records from matching tables.
One matching record in Invoice_detail
and three matching records in Invoice_ticket_detail
. I want to get four records is it possible.
SELECT ez_client.name,
ez_invoice.client_id,
ez_invoice.id,
ez_invoice.invoice_id,
ez_invoice.invoice_date,
ez_invoice_detail.hours as total_hours,
ez_invoice_ticket_detail.hours AS detail_hours,
ez_invoice_detail.amount,
( ez_invoice_ticket_detail.amount - ez_invoice_ticket_detail.tax ) AS total_amount,
ez_invoice_detail.id AS invd_id,
ez_invoice_ticket_detail.id AS itd_id
FROM ez_invoice
LEFT JOIN ez_invoice_detail ON ez_invoice_detail.invoice_id = ez_invoice.id
LEFT JOIN ez_invoice_ticket_detail ON ez_invoice_ticket_detail.invoice_id = ez_invoice.id
JOIN ez_client ON ez_invoice.client_id = ez_client.id
WHERE ez_invoice.is_posted = 1
AND ez_client.company_id = 109
Please suggest.
via Dayachand Patel