Select the number of rows instead of the content in it

Question

Once, I used this query to select the number of items in order to create a list of pages:

SELECT COUNT(id) FROM lists WHERE online = 1

But now it has become difficult, since there are more conditions. Now I need to know the number of lists that are online which have subtitles which have list items in it.

I tried the following, but it gives multiple rows instead of one number:

SELECT COUNT(lists.id)
FROM lists
INNER JOIN subtitles ON subtitles.list_id = lists.id
INNER JOIN listitems ON listitems.subtitle_id = subtitles.id
WHERE lists.online = 1
GROUP BY lists.id

Of course I know that it's possible to calculate the number of lists with PHP, but it would be a lot faster to select the number of lists with MySQL only.

So, is there any way to select the number of rows without getting the actual content of them?

Answer 1

This is probably what you wanted to achieve :

SELECT COUNT(DISTINCT(lists.id))
FROM lists
INNER JOIN subtitles ON subtitles.list_id = lists.id
INNER JOIN listitems ON listitems.subtitle_id = subtitles.id
WHERE lists.online = 1

Instead of grouping the lists, you can simply take the count of distinct within the join. If the count is all you want to get anyway, it's even easier.