my query or i might be wrong?

quote:

---

Originally posted by: Newsgroup User
it is just a precaution against having NULL as totallist value.
but with MySQL as your db you do not really need it since COUNT(expr) in
MySQL will count only non-null values, and return 0 if there are no
matching rows (i.e. if they are all NULL)

as for your problem:

1) what is the value of #url.cid# when you run your original query?

2) are there any records in your grcat table that actually have the
field parentid with the value of url.cid variable? run this query to
check that you actually have the data in your table you think you have
in there:

SELECT grcat.cid, grcat.parentid, grcat.title, COUNT(grlist.listid) AS
totallist
FROM grcat LEFT JOIN grlist ON grcat.cid = grlist.cid
WHERE grlist.active = <cfqueryparam cfsqltype="cf_sql_tinyint" value="1">
GROUP BY
grcat.cid


3) see if this query does what you want it to do:

SELECT grcat.cid, grcat.title, COUNT(grlist.listid) AS totallist
FROM grcat LEFT JOIN grlist ON grcat.cid = grlist.cid AND grlist.active
= <cfqueryparam cfsqltype="cf_sql_tinyint" value="1">
WHERE grcat.parentid = <cfqueryparam cfsqltype="cf_sql_integer"
value="#url.cid#">
GROUP BY
grcat.cid

4) are you sure it is the parentid field you need to compare to #url.cid# ?

5) if nothing above worked, see if this query does:

SELECT grcat.cid, grcat.title, sq_grlist.totallist
FROM grcat
LEFT JOIN
(SELECT grlist.cid, COUNT(grlist.listid) AS totallist FROM grlist WHERE
grlist.active = <cfqueryparam cfsqltype="cf_sql_tinyint" value="1">
GROUP BY grlist.cid) sq_grlist ON grcat.cid = sq_grlist.cid
WHERE grcat.parentid = <cfqueryparam cfsqltype="cf_sql_integer"
value="#url.cid#">
GROUP BY
grcat.cid



Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

---