Results 1 to 5 of 5

Thread: Advanced SQL

  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unanswered: Advanced SQL

    Hello,

    Can someone tell me if it's possible to do the following:

    Have a query thats Searches through the database and finds tables that have 4 records or less

    How can i do this?

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by NickyT View Post
    Can someone tell me if it's possible to do the following:
    not in a single query

    why do you want to do it, may i ask?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2009
    Posts
    3
    Quote Originally Posted by r937 View Post
    not in a single query

    why do you want to do it, may i ask?
    Currently I have a php that has users sign up teams. Each team has multiple members. When we run tournaments such as 4v4's for example we wan't to make sure that every team signed up has at least 4 members otherwise they are to be deleted.

    Currently we have to do this manually, and when there are many teams, this will become extremely tedious

    Thanks.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thank you for the explanation, now i understand why you want to do it

    you have each team in their own table!!

    any chance you can redesign it?

    all teams should be in one table

    then you can do a single query with a simple GROUP BY, with a HAVING on the COUNT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Have you thought of issuing a query against the information_schema database and in particular the TABLES table. This contains all the tables in every database and also includes a field called TABLE_ROWS. For InnoDB this is an approximation rather than an exact number but for MyISAM this appears to be exact.

    Hence, you could simply write the following (assuming you have admin access):

    Code:
    SELECT TABLE_NAME
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = '<database name>'
    AND TABLE_ROWS <= 4;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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