Tuesday, April 11, 2017

Get two join tables all records

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

Advertisement