CODEWITHSUNDEEP
mysql
Saqib
Saqib

Reputation: 1129

Combined WHERE clause for UNION SELECT in mysql Query

I have a query where I use UNION to get data from two tables, it shows correctly but the issue comes when I have to select different dates intervals to show data between those dates, what happens is that it takes only one where clause and apply that over that single SELECT part rather than over whole query. I need that to be applied over wholle ... how?

SELECT
    TICKETS.TICKETID AS TICKET_NO,
    RECEIPTS.DATENEW AS DATENEW,
    PAYMENTS.TOTAL AS MONEY,
    CUSTOMERS.NAME AS CUSTOMER,
    PAYMENTS.PAYMENT AS PAYMENT
FROM RECEIPTS
    LEFT JOIN TICKETS ON RECEIPTS.ID = TICKETS.ID
    LEFT JOIN PAYMENTS ON RECEIPTS.ID = PAYMENTS.RECEIPT
    LEFT JOIN CUSTOMERS ON TICKETS.CUSTOMER = CUSTOMERS.ID
WHERE ?(QBF_FILTER)
UNION SELECT
    ADJUSTMENTS.TICKET_NO AS TICKET_NO,
    ADJUSTMENTS.DATE AS DATENEW,
    ADJUSTMENTS.ADJUSTMENT_AMOUNT AS MONEY,
    ADJUSTMENTS.CUSTOMER_NAME AS CUSTOMER,
    ADJUSTMENTS.ADJUSTMENT_TYPE AS PAYMENT
FROM ADJUSTMENTS
    LEFT JOIN CUSTOMERS ON CUSTOMERS.ID = ADJUSTMENTS.CUSTOMER_ID
    WHERE ?(QBF_FILTER)

>>>> Above query is taken from UNICENTA open source code project, which uses .bs files for generating reports, when I place WHERE clause over both select parts it generates SQL syntax error, now the issue I discussed above is what I need to resolve, thanks :|

Upvotes: 1

Views: 2231

Answers (1)

StaticVoid
StaticVoid

Reputation: 1537

You can accomplish this with a nested Select:

SELECT * FROM (
    SELECT
        TICKETS.TICKETID AS TICKET_NO,
        RECEIPTS.DATENEW AS DATENEW,
        PAYMENTS.TOTAL AS MONEY,
        CUSTOMERS.NAME AS CUSTOMER,
        PAYMENTS.PAYMENT AS PAYMENT
    FROM RECEIPTS
        LEFT JOIN TICKETS ON RECEIPTS.ID = TICKETS.ID
        LEFT JOIN PAYMENTS ON RECEIPTS.ID = PAYMENTS.RECEIPT
        LEFT JOIN CUSTOMERS ON TICKETS.CUSTOMER = CUSTOMERS.ID
    WHERE ?(QBF_FILTER)
    UNION SELECT
        ADJUSTMENTS.TICKET_NO AS TICKET_NO,
        ADJUSTMENTS.DATE AS DATENEW,
        ADJUSTMENTS.ADJUSTMENT_AMOUNT AS MONEY,
        ADJUSTMENTS.CUSTOMER_NAME AS CUSTOMER,
        ADJUSTMENTS.ADJUSTMENT_TYPE AS PAYMENT
    FROM ADJUSTMENTS
        LEFT JOIN CUSTOMERS ON CUSTOMERS.ID = ADJUSTMENTS.CUSTOMER_ID
        WHERE ?(QBF_FILTER)
    )
WHERE (yourDate between Date1 and Date2)

EDIT: I guess it needs to be more like:

SELECT * FROM (
    SELECT
        TICKETS.TICKETID AS TICKET_NO_REC,
        RECEIPTS.DATENEW AS DATENEW_REC,
        PAYMENTS.TOTAL AS MONEY_REC,
        CUSTOMERS.NAME AS CUSTOMER_REC,
        PAYMENTS.PAYMENT AS PAYMENT_REC
    FROM RECEIPTS
        LEFT JOIN TICKETS ON RECEIPTS.ID = TICKETS.ID
        LEFT JOIN PAYMENTS ON RECEIPTS.ID = PAYMENTS.RECEIPT
        LEFT JOIN CUSTOMERS ON TICKETS.CUSTOMER = CUSTOMERS.ID
    UNION SELECT
        ADJUSTMENTS.TICKET_NO AS TICKET_NO_ADJ,
        ADJUSTMENTS.DATE AS DATENEW_ADJ,
        ADJUSTMENTS.ADJUSTMENT_AMOUNT AS MONEY_ADJ,
        ADJUSTMENTS.CUSTOMER_NAME AS CUSTOMER_ADJ,
        ADJUSTMENTS.ADJUSTMENT_TYPE AS PAYMENT_ADJ
    FROM ADJUSTMENTS
        LEFT JOIN CUSTOMERS ON CUSTOMERS.ID = ADJUSTMENTS.CUSTOMER_ID
    )
WHERE ?(QBF_FILTER)

Upvotes: 4

Related Questions