Search code examples
mysqlsqljoinleft-joincorrelated-subquery

MySQL how use correlated query in join

I found, that MySql do not support correlated queries in joins. Is it possible to rewrite provided (simplyfied) query keeping it logic? 

SELECT *
FROM tableX AS x
LEFT JOIN ( 
    SELECT y.xFK, COUNT(y.id) AS y_count
    FROM tableY AS y
    WHERE y.xFK = x.id
    GROUP BY y.xFK
) AS sub
ON sub.xFK = x.id
WHERE sub.y_count > 1

Thanks in advance.

Solution

  • Is this what you want?

    SELECT
  x.*, COUNT(y.ID) AS y_count
FROM tableX x
  LEFT JOIN tableY y
    ON y.xFK = x.ID
GROUP BY
  x.ID
HAVING
  y_count > 1