Geek asks and answers

UNION BEFORE INNER JOINING

Why, when I use UNION before INNER JOIN, like this

    select some_table1.some_column1,
           some_table1.some_column2
    FROM some_table1
    UNION ALL
    select some_table2.some_column1,
           some_table2.some_column2
    FROM some_table2
    INNER JOIN  some_table3
    ON  some_table3.some_column1 = some_table1.some_column1

I have eror: failed to associate the multipart identifier "some_table3.some_column1"? All data types are compatible.

+5
source share
3 answers

You are getting an error because it is invalid SQL syntax. Syntax UNION ALL:

SELECT <column1>
FROM <table1>
UNION ALL
SELECT <column1>
FROM <table2>

You cannot reference columns from any query, as you are trying to do. If you want to reference, then you will want to use something like this:

select *
from 
(
    select some_table1.some_column1, some_table1.some_column2
    FROM some_table1
    UNION ALL
    select some_table2.some_column1, some_table2.some_column2
    FROM some_table2
) t1
INNER JOIN some_table3
ON  some_table3.some_column1 = t1.some_column1
+14
source

You are trying to join some_table1in some_table3when some_table1there is no request in the second half, only the first.

+2
source

, - ?

SELECT
(
       select some_table1.some_column1,
               some_table2.some_column2
        FROM some_table1
        UNION ALL
        select some_table2.some_column1,
               some_table2.some_column2
        FROM some_table2
) sub
        INNER JOIN  some_table3
        ON  some_table3.some_column1 = sub.some_column1
+2

More articles:


All Articles