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 3 of 3
  1. #1
    New Coder
    Join Date
    Apr 2011
    Posts
    59
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Need help with a specific query

    Hey guys, I've tried playing around with something, and I can't find the answer.

    I need a query to help me keep my site clean. It needs to look something like this:

    SELECT * FROM 'table' WHERE 'id' is the same AND number of records > 3

    So let's say my table looks like this:

    ID NAME
    1 John
    1 James
    1 Alex
    2 Kira
    2 Mike
    2 Nancy
    2 Elenora
    3 Raymond
    3 Taylor

    --------------

    Only ID 2 should be returned because it is the only ID with more then 3 records. I would just loop through with a mysqli_num_rows, but with the size of the table it takes way too long and it not a healthy way to do it.

    Is it even possible to do this? I've tried google, but I can't find anything. I would appreciate any help.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    Trivial.

    Code:
    SELECT id, COUNT(*) AS howmany
    FROM table 
    GROUP BY id
    HAVING howmany > 3
    If you want all the info (not just the ID's) then you have to join back to the original table, again:
    Code:
    SELECT T.* FROM table AS T,
           ( SELECT id, COUNT(*) AS howmany
             FROM table 
             GROUP BY id
             HAVING howmany > 3 ) AS SUBS
    WHERE T.id = SUBS.id
    ORDER BY T.id, T.name
    Finally, if you want to DELETE all the records where the count is 3 or less, you do similar:
    Code:
    DELETE FROM table 
    WHERE id IN (
             SELECT id
             FROM table 
             GROUP BY id
             HAVING COUNT(id) <= 3 )
    Last edited by Old Pedant; 08-26-2011 at 03:14 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Kurisvo (08-26-2011)

  • #3
    New Coder
    Join Date
    Apr 2011
    Posts
    59
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Old Pedant, you are a lifesaver! I don't know how to thank you. But thank you so much.


  •  

    Posting Permissions

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