开发者

Correlated mysql subqueries

HI i want to get the value of the variabl开发者_JS百科e 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??


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.

  • select * with group by
  • order by in a subselect
  • using the outer select in the subselect (your question)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜