Combine two python mysql queries with a condition

I want to grab some specific rows of two tables before and after a certain time, this is my code:

mysql_query = """
select tb1x.id
from table1 as tb1x 
join table2 as tb2x ON tb2x.id = tb1x.cid
where tb1x.created > CURDATE()
UNION ALL
(select tb1y.id
from table1 as tb1y 
join table2 as tb2y ON tb2y.id = tb1y.cid
where tb1y.created < CURDATE()
)
where tb2x.id=tb2y.id
ORDER BY tb1y.created DESC;"""

It gives an error and indicates that UNION is not used properly.

and I guess the problem could be here: AND tb2x.id=tb2y.id Could you please help where is the problem or if it’s an smart way to compare two different sections of a table?

Answer

UNION doesn’t permits any comparison between columns, so where tb2x.id=tb2y.id after a UNION is an error

https://en.wikipedia.org/wiki/Set_operations_(SQL)#UNION_operator

To compare two different sections of a table you have to JOIN itself:

SELECT *
FROM (
       SELECT
         tb1x.id,
         tb1x.created
       FROM table1 AS tb1x
         JOIN table2 AS tb2x
           ON tb2x.id = tb1x.cid
       WHERE tb1x.created > CURDATE()
     ) AS tb3x
  JOIN
  (
    SELECT
      tb1y.id,
      tb1y.created
    FROM table1 AS tb1y
      JOIN table2 AS tb2y
        ON tb2y.id = tb1y.cid
    WHERE tb1y.created < CURDATE()
  ) AS tb3y

    ON tb3x.id = tb3y.id
ORDER BY tb3y.created DESC;

Another thing is collect all rows in one table before and after a certain time. You need to use UNION of two sub-query table, each of which is a filter of the input table:

SELECT *
FROM (
  (
    SELECT
      tb1x.id,
      tb1x.created
    FROM table1 AS tb1x
      JOIN table2 AS tb2x
        ON tb2x.id = tb1x.cid
    WHERE tb1x.created > CURDATE()
  )
  UNION ALL
  (
    SELECT
      tb1y.id,
      tb1y.created
    FROM table1 AS tb1y
      JOIN table2 AS tb2y
        ON tb2y.id = tb1y.cid
    WHERE tb1y.created < CURDATE()
  )
)
ORDER BY created DESC;