Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm writing a script that gets a count of rows for a few tables, however for some tables I want to only get a count of rows where a flag is set (in this case active=1). Is there a way I can do this in one query?

Eg:

Table users has a column called active

Table clients does not have a column called active

I want to get a count of users where active=1 and just get a count of clients.

Before you say "just hard code it" this is a query that's going inside a python script that could be run on numerous different databases and I have no way of knowing what tables my script will be selecting and if they have a column called active, and I would prefer to have just one query to do it all instead of two separate ones and relying on mysql to throw an error so I know to use the other one.

share|improve this question
    
Please post SHOW CREATE TABLE users\G and SHOW CREATE TABLE clients\G and tell us if there is a userid in the clients table that references a user, or if a clientid in the user table that references a client. –  RolandoMySQLDBA Jun 19 '13 at 17:55
    
there's a clientid in user that references a client –  Matt S. Jun 19 '13 at 17:56
    
I need blunt clarification: In every database, does the users table have an active column? –  RolandoMySQLDBA Jun 19 '13 at 18:25
add comment

1 Answer

up vote 9 down vote accepted

My first thought would be to use the INFORMATION_SCHEMA first, so you get to know (in one query for all tables in the MySQL instance) which tables have an active column and then use that info to construct your queries. And this is probably the most sane approach.

There is one other, tricky way though that works no matter if the table has or not such a column:

SELECT 
  ( SELECT COUNT(*)
    FROM TableName AS t
    WHERE active = 1
  ) AS cnt
FROM
  ( SELECT 1 AS active
  ) AS dummy ;

Tested at SQL-Fiddle How it works?

If the table has a column named active, the query is "translated" as if it had:

    WHERE t.active = 1

If the table doesn't have a column named active, the query is "translated" as if it had:

    WHERE dummy.active = 1         -- which is true 
share|improve this answer
2  
Simply brilliant. (And brilliantly simple.) –  Andriy M Jun 19 '13 at 18:33
2  
That's magical! –  Matt S. Jun 19 '13 at 18:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.