Search code examples
mysqljoinreferencecorrelated-subquery

Searching workaround for mysql limitation to reference top level query over 2 levels

For the following intended mysql/mariadb query:

SELECT * FROM aktfv f
LEFT JOIN
(SELECT * FROM (
       SELECT * FROM labor1a WHERE pat_id = f.pat_id
 UNION SELECT * FROM labor2a WHERE pat_id = f.pat_id) i
) i ON i.pat_id = f.pat_id;

I get error 1054: Unknown column 'f.pat_id' in 'where clause',
apparently because of the limitation to refer to an outer table parameter only over 1 level.
I wanted to apply the 'where' clause because otherwise the 'union select' takes very very long.
Can anybody give me a hint for a workaround?

Solution 
  • SELECT  f.* ,l.* FROM aktfv f join labor1a l on  l.pat_id = f.pat_id
 UNION 
SELECT  f.* ,l.* FROM aktfv f join labor2a l on l.pat_id = f.pat_id

    Or am I missing something?