mysql - 发票类型从表中获取查询

Question

ER diagram

附上ER图

我需要获取销售发票invoices.type = 'sales' 和invoices.type = 'purchase' 带有公司名称，如您在选择语句中看到的那样还有销售和购买栏

我的查询

SELECT jobs.id as jobID,
         -- c.name AS Customer,jobs.job_no, 
             invoices.invoice_due_date,
             jobs.id, 
                ( (invoice_items.amount * invoice_items.quantity * invoice_items.exchange_rate )) as Ammount
FROM invoices
INNER JOIN `invoice_items` ON invoices.id = invoice_items.invoice_id 
INNER JOIN `jobs`                ON jobs.id = invoices.job_id


 -- INNER JOIN `company` as c ON  c.company_id  =   jobs.company_id  -- jobs and company.


WHERE invoices.type = 'sales'

--   new conditions
    AND MONTH(jobs.created_at) = '3'
    AND YEAR(jobs.created_at) = '2017' 
    AND jobs.status > 0
    AND jobs.complete_job > 0
    AND jobs.completed_at IS NOT NULL

    AND   invoice_items.deleted_at IS NULL
GROUP BY  jobs.job_no
ORDER BY  invoices.invoice_due_date DESC

Answer 1

尝试这个更新...

SELECT jobs.id as jobID,
    C.name AS Customer,jobs.job_no, 
    invoices.invoice_due_date,
    jobs.id, 
    ( (invoice_items.amount * invoice_items.quantity *invoice_items.exchange_rate )) as Ammount,
    CASE invoices.type 
       WHEN 'sales' THEN 'S' 
    ELSE 'P' 
    END as trans_type 
FROM invoices
INNER JOIN `invoice_items` ON invoices.id = invoice_items.invoice_id 
INNER JOIN `jobs`                ON jobs.id = invoices.job_id
LEFT OUTER JOIN `company` as C ON  C.company_id = jobs.company_id
WHERE (invoices.type = 'sales' OR invoices.type = 'purchase') AND (MONTH(jobs.created_at) = '3') AND (YEAR(jobs.created_at) = '2017') AND (jobs.status > 0) AND (jobs.complete_job > 0) AND (jobs.completed_at IS NOT NULL)