I have a table with fields id, parentid, userid, and rowtype. I have
joined it with itself where one parent row has a one to many
relationship with children rows. I need to create a query which will
return the number of children rows, but only if a particular userid is
not contained in the counted rows and the count is less than 6. I've
got it returning a result that will give me the count... but how do I
make sure that a particular value is not in the userid in any of those
counted fields (without doing another query)?

SELECT COUNT (table1.parentid) AS count_children LEFT OUTER JOIN table
table1 ON (table.id=table1.parentid) WHERE table.rowtype = 1 AND
table1.rowtype = 2 HAVING COUNT(table1.parentid) <=6

On 31-12-10 08:31, keytostars wrote:
add something to the WHERE-clause



--
Luuk

keytostars <starkeypictures (AT) gmail (DOT) com> wrote:

SELECT COUNT(*) AS count_children,
GROUP_CONCAT(
IF(user_id = $forbidden, '1', '0')
SEPARATOR ''
) AS bitmask
FROM $table
WHERE parent_id = $parent
GROUP BY parent_id
HAVING count_children <= 6
AND bitmask NOT LIKE '%1%'


XL