Using values from main query inside a subquery

Question

I had a problem creating a MySQL query with a subquery. I wanted to use some data from the main query on the subquery, as many times did. But this time I wanted to use it in a JOIN and didn't worked. I really want to understand why this happens.

I will show you some examples that works and the one that didn't.

I made this simple structure to reproduce the example:

# table1
id   field1
1    *first_value*
2    *another_value*

#table2
id   field2
1    *second_value*

Using table1.id on the WHERE of the subquery to get a value, the most typical use for me (I know this can be a join, but i try to show the difference):

SELECT 
    t1.field1,
    (
        select t2.field2
        FROM table2 as t2
        WHERE t2.id = t1.id    
    ) as field2
FROM table1 as t1
WHERE t1.id = '1';

You can use table1.id on the SELECT part too (not much sense in the example, but works):

SELECT 
    t1.field1,
    (
        select t1.id as field2
        FROM table2 as t2
        WHERE t2.id = t1.id 
    ) as field2
FROM table1 as t1
WHERE t1.id = '1';

Now, if you try to use it on a JOIN inside the subquery, then, crashes:

SELECT 
    t1.field1,
    (
        select t1.id
        FROM table2 as t2
            LEFT JOIN table1 as t3 ON t3.id = t1.id
        WHERE t2.id = t1.id        
    ) as field2
FROM table1 as t1
WHERE t1.id = '1';

Kernel error: Error( 1054 ) 42S22: "Unknown column 't1.id' in 'on clause'"

Buuut, u can do the JOIN using the field in another subquery changing ON t3.id = t1.id to ON t3.id = (SELECT t1.id) ???

SELECT 
    t1.field1,
    (
        select t1.id
        FROM table2 as t2
            LEFT JOIN table1 as t3 ON t3.id = (SELECT t1.id)
        WHERE t2.id = t1.id        
    ) as field2
FROM table1 as t1
WHERE t1.id = '1'

I wonder to know why the third example query doesn't work while all others does. Can someone explain this, please?

Thank you :)

Answer 1

That's because all elements in the ON clause of a JOIN, must belong the one of the joined tables, so as your t2.id must be equal to t1.id, you can do

SELECT 
    t1.field1,
    (
        select t1.id
        FROM table2 as t2
            LEFT JOIN table1 as t3 ON t3.id = t2.id
        WHERE t2.id = t1.id        
    ) as field2
FROM table1 as t1
WHERE t1.id = '1';