Advanced SQL

NickyT · #1 (**permalink**) 12-04-10, 16:13

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.

r937 · #2 (**permalink**) 12-04-10, 17:26

Quote:

Originally Posted by NickyT

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?

NickyT · #3 (**permalink**) 12-04-10, 23:15

Quote:

Originally Posted by r937

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.

r937 · #4 (**permalink**) 12-05-10, 05:19

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

it-iss.com · #5 (**permalink**) 12-13-10, 12:14

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;

Internet Services

Web Development

Digital Marketing

Consumer Tech