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!
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).