Combine two python mysql queries with a condition

Question

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?

Are you sure you want a UNIION and not something like a JOIN? — Sam Chats
– Sam Chats, Commented Aug 30, 2019 at 14:28
Put the individual select queries inside parentheses (..). Due to order by clause, MySQL is unable to interpret properly. — Madhur Bhaiya
– Madhur Bhaiya, Commented Aug 30, 2019 at 14:29
Not sure about UNION but I want different tb1x.ids after and before a certain time — J2015
– J2015, Commented Aug 30, 2019 at 14:30
@MadhurBhaiya then does query realizes AND tb2x.id=tb2y.id? — J2015
– J2015, Commented Aug 30, 2019 at 14:34
Yes. There must be parentheses around the individual selects .. like : (select .... Where..) union (select ... Where...) order by.. — Madhur Bhaiya
– Madhur Bhaiya, Commented Aug 30, 2019 at 14:36

A. Rosano · Accepted Answer · 2019-08-30 15:00:16Z

1

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;

answered Aug 30, 2019 at 15:00

A. Rosano

464 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

J2015 Over a year ago

I need a kind of UNION ALL which gives me the posibility to check ON tb3x.id = tb3y.id, however none of your answer will take me to that point!

A. Rosano Over a year ago

Can you explain me with a data input/output example?

J2015 Over a year ago

I think the problem is not because of input/output, for example when I use UNION ALL it gives me this error : Every derived table must have its own alias which is related to as whatever

J2015 Over a year ago

I could fix the problem by combining both union method and join method!

A. Rosano Over a year ago

Can you share the fix?

Collectives™ on Stack Overflow

Combine two python mysql queries with a condition

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related