Invalid SQL Query

Question

I have the next query that in my opinion is a valid one, but I keep getting error telling me that there is a proble on "WHERE em.p4 = ue.p3" - Unknown column 'ue.p3' in 'where clause'.

This is the query:

SELECT DISTINCT ue.p3
FROM 
    table1 AS ue INNER JOIN table2 AS e 
    ON ue.p3 = e.p3 
    WHERE 
        EXISTS(
            SELECT 1 FROM (
                SELECT (COUNT(*) >= 1) AS MinMutual 
                FROM table4 AS smm 
                WHERE 
                    smm.p1 IN (
                        SELECT sem.p3 FROM table3 AS sem 
                        INNER JOIN table2 AS em ON sem.p3 = em.p3 
                        WHERE em.p4 = ue.p3 AND 
                        sem.type = 'friends' AND em.p2 = 'normal' ) AND 
                    smm.p5 IN (
                        15000,15151
                    )
            ) AS Mutual WHERE 
        Mutual.MinMutual = TRUE) LIMIT 11

If I execute the sub-query which is inside the EXISTS function, everything is O.K.

PLEASE HELP!

Answer 1

AFAIK, this kind of correlated query is not doable in mysql as of now. Join to a derived table as opposed to using the exists.

Answer 2

The reason for the error is that you can only reference one subquery layer down when correlating. Look at where the ue alias is defined, and count the number of FROM clauses until to you reach the next reference.

I re-wrote your query as:

SELECT DISTINCT ue.p3
  FROM table1 AS ue 
  JOIN table2 AS e ON ue.p3 = e.p3 
 WHERE EXISTS(SELECT 1 AS MinMutual 
                FROM table4 AS smm 
                JOIN TABLE3 sem ON sem.p3 = smm.p1
                               AND sem.type = 'friends'
                JOIN TABLE2 em ON em.p3 = sem.p3
                              AND em.p3 = ue.p3
                              AND em.p2 = 'normal'
               WHERE smm.p5 IN (15000,15151)
            GROUP BY ? --needs a group by clause, in order to use HAVING
              HAVING COUNT(*) >= 1)  
 LIMIT 11

EXISTS returns true if satisfied -- it doesn't evaluate based on the subquery returning "true". There's no need for the additional subquery you have (which was causing problems anyway).