mysqlsqlcorrelated-subquery

Correlated mysql subqueries

HI i want to get the value of the variable from the main query inside the sub query

SELECT t1.*,sq.*,count(distinct(t4.col1)) as count,
FROM t1 LEFT OUTER JOIN( 
                         SELECT t2.col1,
                                t2.col2,
                                t2.col3 
                          FROM t2 
                          WHERE   t2.col1=t1.col1            
                          ORDER BY t2.col2 DESC, t2.col1 DESC
                        ) as sq 
         ON sq.col1=t1.col1  
LEFT OUTER JOIN t3 ON t3.col1=t1.col4 
LEFT OUTER JOIN t4 ON t4.col1=t1.col4 
WHERE t3.col2=x 
GROUP BY t1.col3 
LIMIT 15

How do i get the value of the variable t1.col1 inside the subquery sq when I am joining it on the same column??

Solution

  • You can't but following should return equivalent results as the query you've posted.

    SELECT  t1.col3            
FROM    t1 
        left outer join ( 
          select  t2.col1
                  ,t2.col2
                  ,t2.col3 
                  ,t2.col4
          FROM    t2 
        ) as sq on sq.col1 = t1.col1 
                   AND sq.col4 = t1.col1
where   t1.col2 = x 
group by 
        t1.col3 
limit 15

    Note: the original query you've posted contains a few syntax errors.