SELECT COUNT from mutiple tables

[Psycho]

I want to get the count of records from separate tables. Is it possible to do this with a single query?

Currently I am trying the following:

SELECT COUNT(g.genre_id) as genres, COUNT(m.movie_id) as movies\n"
FROM genres g, movies m

But instead of getting the values I expect where genres = the number of records in the genres table & movies = the number of records in the movies table; I am getting the same value for both which is the value of movies * genres.

So is it possible to pull records from separate tables w/o associating them or is there another way to run the query to get what I want. or should I just run separate queries (there are about 5 or 6 table in all - just included two for illustration purposes).

[fenway]

Well, even if you use subqueries, the DB would still run multiple queries, but you'd only have to issue a single DB statement, if that's what you're after.

[Psycho]

I figured it out myself

[code]SELECT COUNT(DISTINCT m.movie_id) as movies,
        COUNT(DISTINCT g.genre_id) as genres,
        COUNT(DISTINCT s.studio_id) as studios
FROM movies m, genres g, studios s[/code]

[fenway]

That's incredibly ineffecient... you're returning N^3 records for no reason, and then using DISTINCT which forces the use of a temporary table... check EXPLAIN and you'll see.

It's much better to use:

[code]
SELECT
( SELECT COUNT(movie_id) FROM movies ) as movies,
( SELECT COUNT(genre_id) FROM movies ) as genres,
( SELECT COUNT(studio_id) FROM movies ) as studios
[/code]

[Psycho]

OK, great. Thank you. I knew there had to be a better way.