Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts

    Exclamation mysql query problem ignores count fields with no records

    Hi All

    i have this query
    PHP Code:
    SELECT * , COUNTprod.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`.`deptNameDESC
    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

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Code:
    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:


    Code:
    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.
    Last edited by BubikolRamios; 06-28-2011 at 05:44 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Allso I think, from start , this should do the same as your code, without subselect
    Code:
    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
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Quote Originally Posted by BubikolRamios View Post
    Code:
    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.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    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:
    Code:
    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:
    Code:
    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

  • #6
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •