...

View Full Version : mysql query problem ignores count fields with no records



LJackson
06-28-2011, 04:16 PM
Hi All

i have this query

SELECT * , COUNT( prod.deptID ) AS count
FROM tbl_product_departments AS prod
LEFT JOIN (
SELECT *
FROM tbl_departments
WHERE deptParent = '2'
) AS dept ON dept.deptID = prod.deptID
GROUP BY prod.deptID
ORDER BY `dept`.`deptName` DESC
LIMIT 0 , 30

which is good but it has one problem and that is if the count field = 0 then it doesnt return the deptName which is not what i want i want all the deptNames to be returned even if the count = 0

any ideas please?
thanks
Luke

BubikolRamios
06-28-2011, 05:40 PM
SELECT deptID,COUNT( deptID ) AS count,deptName,
FROM tbl
GROUP BY deptID


what you get here is count, grupped by deptID.
All the rest is randome, forinstance:




deptID deptName
1 A
1 B
1 C


query from that could return
1 3 A
or
1 3 B
etc

Unless you decide to do group_concat(...deptName), to see all that is realy there

* is bad idea, as count is also bad idea.

BubikolRamios
06-28-2011, 05:48 PM
Allso I think, from start , this should do the same as your code, without subselect


SELECT * , COUNT( prod.deptID ) AS count
FROM tbl_product_departments AS prod
LEFT JOIN tbl_departments ON dept.deptID = prod.deptID and dept.deptParent = '2'
GROUP BY prod.deptID
ORDER BY `dept`.`deptName` DESC
LIMIT 0 , 30

Old Pedant
06-28-2011, 11:30 PM
SELECT * , COUNT( prod.deptID ) AS count
FROM tbl_product_departments AS prod
LEFT JOIN tbl_departments ON dept.deptID = prod.deptID and dept.deptParent = '2'
GROUP BY prod.deptID
ORDER BY `dept`.`deptName` DESC
LIMIT 0 , 30

This makes no sense.

Because the GROUP BY is on the *SAME FIELD* that the COUNT() is of.

I don't see now you ever get sensible results from that.

Old Pedant
06-28-2011, 11:39 PM
You need to be grouping on some *OTHER* field than the one you want the count of.

Or, more simply, use COUNT(*) and then group by deptid.

This should work:


SELECT prod.fiedl1, prod.field2, prod.field3, prod.deptid,
dept.deptname, dept.fieldX, dept.fieldY, S.count
FROM tbl_product_departments AS prod
INNER JOIN ( SELECT deptid, COUNT(*) AS count GROUP BY deptid ) AS S
ON prod.deptid = S.deptid
LEFT JOIN tbl_departments AS dept
ON ( deptParent = 2 AND dept.deptID = prod.deptID )
ORDER BY dept.deptName DESC
LIMIT 0 , 30

probably could also do it thus:


SELECT prod.field1, prod.field2, prod.field3, prod.deptid, prod.count,
dept.deptname, dept.fieldX, dept.fieldY
FROM ( SELECT field1, field2, field3, deptid, COUNT(*) AS count
FROM tbl_product_departments GROUP BY deptid ) AS prod
LEFT JOIN tbl_departments AS dept
ON ( deptParent = 2 AND dept.deptID = prod.deptID )
ORDER BY dept.deptName DESC
LIMIT 0 , 30

LJackson
06-29-2011, 04:50 PM
hi guys,

sorry for the lack of replys from myself :( i will spend some time later on this evening and try out your suggestions...

many thanks for your time
Luke



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum