#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0

    Cool Count from a boolean field


    a short schema of the database

    Code:
    system
      pksystem
      ..and many information fields
    user
      pkuser
      ..and many user fields
    user_system
      pkuser_system
      fksystem
      fkuser
      admin (boolean 0 or 1)
      ...and many more
    My problem is now, i will see all Systems from the user_system Table where are the count of admins is not higher 2

    the first syntax from me

    Code:
    SELECT * 
    FROM 
    user_system us
    LEFT JOIN 
    system s 
    ON us.fksystem = s.pksystem
    GROUP BY 
    us.fksystem,
    admin 
    HAVING 
    count(us.admin) < 2 
    ORDER BY 
    admin
    UPDATE other SQL Statement (maybe this is correct)
    Code:
    SELECT
     fksystem,
    sum(admin) sum_admin 
    FROM 
    `user_system` 
    GROUP BY 
    fksystem 
    HAVING 
    sum_admin < 2 
    ORDER BY 
    sum_admin
    Last edited by Guelphdad; November 21st, 2012 at 02:36 PM. Reason: replaced mysql tags with code tags
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    the second query is correct --
    Code:
    SELECT fksystem
         , SUM(admin) AS sum_admin 
      FROM user_system
    GROUP 
        BY fksystem 
    HAVING sum_admin < 2 
    ORDER 
        BY sum_admin
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by r937
    the second query is correct --
    Code:
    SELECT fksystem
         , SUM(admin) AS sum_admin 
      FROM user_system
    GROUP 
        BY fksystem 
    HAVING sum_admin < 2 
    ORDER 
        BY sum_admin
    thanks for your opinion...use now this sql query without scruple

IMN logo majestic logo threadwatch logo seochat tools logo