Cosmin Cosmin - 28 days ago 11
MySQL Question

Error when ordering an UNION MySQL query

The following query throws an error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION (' at line 15
.

Here's the query:

(SELECT 'receivables' AS entry_type,
rp.amount_recieved AS amount,
rp.rp_id AS payment_document_id,
rp.paidwith AS payment_type,
rp.payment_doc_date AS payment_document_date,
r.id AS document_id,
c.comp_name AS company
FROM receivables_payments rp,
receivables r,
comp_companies c
WHERE rp.r_id = r.id
AND r.supplier = c.comp_id
AND r.beneficiary_id = '1'
AND rp.payment_doc_date BETWEEN '2016-10-01' AND '2016-11-09')
UNION
(SELECT 'payables' AS entry_type,
pp.amount_recieved AS amount,
pp.pp_id AS payment_document_id,
pp.paidwith AS payment_type,
pp.payment_doc_date AS payment_document_date,
p.id AS document_id,
c.comp_name AS company
FROM payables_payments pp,
payables p,
comp_companies c
WHERE pp.p_id = p.id
AND p.supplier = c.comp_id
AND p.beneficiary_id = '1'
AND pp.payment_doc_date BETWEEN '2016-10-01' AND '2016-11-09')
ORDER BY payment_document_date DESC


Can please somebody tell me what's wrong with it? Because I fail to see it.

Thank you.

Answer

Try ordering it in outer query

SELECT * FROM (
(SELECT 'receivables'       AS entry_type, 
        rp.amount_recieved  AS amount, 
        rp.rp_id            AS payment_document_id, 
        rp.paidwith         AS payment_type, 
        rp.payment_doc_date AS payment_document_date, 
        r.id                AS document_id, 
        c.comp_name         AS company 
 FROM   receivables_payments rp, 
        receivables r, 
        comp_companies c 
 WHERE  rp.r_id = r.id 
        AND r.supplier = c.comp_id 
        AND r.beneficiary_id = '1' 
        AND rp.payment_doc_date BETWEEN '2016-10-01' AND '2016-11-09') 
UNION 
(SELECT 'payables'          AS entry_type, 
        pp.amount_recieved  AS amount, 
        pp.pp_id            AS payment_document_id, 
        pp.paidwith         AS payment_type, 
        pp.payment_doc_date AS payment_document_date, 
        p.id                AS document_id, 
        c.comp_name         AS company 
 FROM   payables_payments pp, 
        payables p, 
        comp_companies c 
 WHERE  pp.p_id = p.id 
        AND p.supplier = c.comp_id 
        AND p.beneficiary_id = '1' 
        AND pp.payment_doc_date BETWEEN '2016-10-01' AND '2016-11-09')) t1
ORDER  BY payment_document_date DESC;

Hope this should solve your problem.