In article <[EMAIL PROTECTED]>,
Eric Jensen <[EMAIL PROTECTED]> writes:
> So you want 5 contacts for every user? Try this:
> SELECT COUNT(c.id) AS count, u.username, u.first_name, u.last_name,
> c.name
> FROM user AS u, contact AS c
> WHERE u.id = c.id_user
> GROUP BY c.id_user
> HAVING count <= 5
This won't work since "count" would be the total number of contacts
for this user.
Try something like that:
SELECT u.username, c1.name
FROM user AS u
JOIN contact AS c1 ON u.id = c1.id_user
LEFT JOIN contact AS c2 ON c2.id_user = c1.id_user
AND c2.name < c1.name
GROUP BY u.username, c1.name
HAVING count(c2.id) < 5
ORDER BY u.username, c1.name
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
