MYSQL UNION AND WHERE CONDITION

MySQLunionwhere

I am unable to put a WHERE condition for a UNION in MYSQL.
Now with the below SQL line I am getting all the records in the tables and not filtering date between 2 dates.

My order by is working fine. I want to do this without creating a view

    SELECT *
    FROM DCHDR AS H
    LEFT JOIN DCDTL AS T ON H.DC_No = T.DC_Nos
    UNION
    SELECT *
    FROM DCHDR AS H
    RIGHT JOIN DCDTL AS T ON H.DC_No = T.DC_Nos
    WHERE H.DC_date
    BETWEEN '2013-03-02'
    AND '2013-03-11'
    ORDER BY `Product_Desc` ASC
    LIMIT 510 , 30

Best Answer

Unlike ORDER BY, WHERE clauses in a union affect only that part of the union (the single SELECT query it is attached to), and not the entire union.

Either put your union in a subquery and filter on the results:

SELECT *
FROM
(
    SELECT *
    FROM DCHDR AS H
    LEFT JOIN DCDTL AS T ON H.DC_No = T.DC_Nos
    UNION
    SELECT *
    FROM DCHDR AS H
    RIGHT JOIN DCDTL AS T ON H.DC_No = T.DC_Nos
) H
WHERE H.DC_date BETWEEN '2013-03-02' AND '2013-03-11'
ORDER BY `Product_Desc` ASC
LIMIT 510, 30

Or duplicate your condition for each part:

SELECT *
FROM DCHDR AS H
LEFT JOIN DCDTL AS T ON H.DC_No = T.DC_Nos
WHERE H.DC_date BETWEEN '2013-03-02' AND '2013-03-11'
UNION
SELECT *
FROM DCHDR AS H
RIGHT JOIN DCDTL AS T ON H.DC_No = T.DC_Nos
WHERE H.DC_date BETWEEN '2013-03-02' AND '2013-03-11'
ORDER BY `Product_Desc` ASC
LIMIT 510 , 30

It looks though that you are trying to simulate a full join. Might I suggest the following query instead, which should be much faster (and actually do what you want, which is not filtering the second part of the union):

SELECT *
FROM DCHDR AS H
LEFT JOIN DCDTL AS T ON H.DC_No = T.DC_Nos
WHERE H.DC_date BETWEEN '2013-03-02' AND '2013-03-11'
UNION ALL
SELECT *
FROM DCHDR AS H
RIGHT JOIN DCDTL AS T ON H.DC_No = T.DC_Nos
WHERE H.DC_No IS NULL
ORDER BY `Product_Desc` ASC
LIMIT 510 , 30

Note if you have duplicate rows in DCDTL that you are purposely trying to filter out with UNION, you can change the UNION ALL to UNION, but I suspect this is actually not the case.

Related Topic