Geek Answers Handbook

Mysql primary access column inside a right join subquery

SELECT id,region FROM ads RIGHT JOIN (SELECT * FROM ads a2 WHERE a2.region=ads.region LIMIT 4) AS regions ON regions.id=ads.id

Says "unknown column ads.region". How to access the column area of ​​the main table?

I need to get 4 rows for each area

+1
source share
1 answer

Using:

  SELECT a.id, a.region FROM ads a RIGHT JOIN (SELECT t.*, CASE WHEN @region = t.region THEN @rownum := @rownum + 1 ELSE @rownum := 1 END as rank, @region := t.region FROM ADS t JOIN (SELECT @rownum := 0, @region := 1) r ORDER BY t.region) b ON b.id = a.id AND b.region = a.region AND b.rank <= 4
+3
source

More articles:


All Articles