select in select sql
SELECT column1 = (SELECT column-name FROM table-name WHERE condition), column-names FROM table-name WEHRE condition
Source: www.dofactory.com
nested select sql
'select *,(SELECT count(id) FROM products WHERE user_id = users.id) as products_count from users ORDER BY products_count DESC, ID DESC LIMIT 200