CODEWITHSUNDEEP
mysql
Nino
Nino

Reputation: 707

Compare fields over union

I would like to UNION two tables and compare a field across them. The system I run the queries in do not allow multi queries and setting up variables. The code below generates an error on the WHERE clause. Is there an alternative way for doing that on MySQL 5.7?

SELECT * FROM (

    SELECT 
        t1.field1, 
        t1.field2 
    FROM table_1 t1

    UNION

    SELECT 
        t2.field1, 
        t2.field2
    FROM table2 t2
    WHERE t2.field2 = t1.field2

) AS bar

Upvotes: 1

Views: 191

Answers (1)

nbk
nbk

Reputation: 49375

This query gives you the same result.

SELECT * FROM (

    SELECT 
        t1.field1, 
        t1.field2 
    FROM table_1 t1

    UNION

    SELECT 
        t2.field1, 
        t2.field2
    FROM table2 t2
    WHERE t2.field2 in (SELECT field2  FROM table_1)

) AS bar

If you have a WHERE Clause on t1 you have to add also in the subquery

Upvotes: 1

Related Questions