I want to grab some specific rows of two tables before and after a certain time, this is my code:
x
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?
Advertisement
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;