Kako dohvatiti count() iz subquery-ja?

[ivanhoe]

Trazim duplicate records, ali mi treba da znam i koliko duplikata ima za svaki (plus bi bilo dobro da po tome uradim ORDER BY).

Za sad imam ovakav upit koji nalazi sve usere koji imaju isti payment email:

SELECT u.`id`, u.`username`, u.`email`, u.`first_name`, u.`last_name`, mb.`moneybookers_email` 
FROM users u JOIN `payment` mb ON u.id=mb.`user_id`
WHERE mb.`moneybookers_email` IN (
     SELECT `moneybookers_email` FROM `payment`     
     GROUP BY `moneybookers_email` HAVING count(*)>1
)
ORDER BY  mb.`moneybookers_email`

Jel postoji nacin da dohvatim vrednost count(*) u spoljnom upitu?

[ajankovic]

Nije testirano ali čisto da ti ukažem na drugačiji pristup pa ti napravi izmene kako tebi odgovara:

SELECT u.`id`, u.`username`, u.`email`, u.`first_name`, u.`last_name`, mb.`moneybookers_email`, m.payments_count
FROM `users` u
INNER JOIN (
SELECT `user_id`, count(*) as payments_count, `moneybookers_email` FROM `payment`
GROUP BY `moneybookers_email` HAVING payments_count>1
) mb ON u.id=mb.`user_id`
ORDER BY mb.`moneybookers_email`

[ivanhoe]

Da, tako nesto sam i ja skuckao po preporuci ljudi da koristim JOIN umesto IN...

Ispalo je ovako, ako nekom zatreba:

SELECT u.`id`, u.`username`, u.`email`, u.`first_name`, u.`last_name`, sub.`moneybookers_email`, sub.cnt 
FROM users u JOIN `payment` mb ON u.id=mb.`user_id`
JOIN
    (SELECT count(*) AS cnt, `moneybookers_email` FROM `payment`
    GROUP BY `moneybookers_email` HAVING cnt>1 ) sub
ON mb.`moneybookers_email`=sub.`moneybookers_email` 
ORDER BY sub.cnt DESC, mb.`moneybookers_email`

[robi-bobi]

WHERE ... IN (
SELECT

radi fino kad nemas puno record-a.
Mysql pocinje da se muci ako brojka dostigne vise nula.
Oracle, pak, ima limit, koliko je moguce recorda staviti u IN() - nesta oko 1000 ili 10000
Tako da je JOIN i zbog toga preporucljiviji.