CODEWITHSUNDEEP
mysql
dotslashlu
dotslashlu

Reputation: 3401

How to count results in a join query

select music.*
 ,count(*) as count
FROM music
  JOIN music_tagmap ON music.id = music_tagmap.music_id
  JOIN tag ON music_tagmap.tag_id=tag.id
   WHERE tag.content = ...
   LIMIT 10 OFFSET 0;

To count the result, I first came up with adding line 2, but adding line 2 only return one row of the results and the count. How to get both of the results and the count in conditions like this?

Upvotes: 1

Views: 127

Answers (2)

Rob Williams
Rob Williams

Reputation: 1470

Change your query to:

select SQL_CALC_FOUND_ROWS music.*
FROM music
  JOIN music_tagmap ON music.id = music_tagmap.music_id
  JOIN tag ON music_tagmap.tag_id=tag.id
   WHERE tag.content = ...
   LIMIT 10 OFFSET 0;

and then run a second query after it's finished:

select FOUND_ROWS();

That will give you the number of rows selected in the first query as if there was no LIMIT statement.

Upvotes: 1

Byron
Byron

Reputation: 429

You are probably better off just doing a second query to get the count. You could try embedding a "SELECT COUNT()" subquery in place of "count(*) as count)", but I don't think you're going to reduce the overhead a lot by embedding the subquery...it may even increase processing overhead.

Upvotes: 0

Related Questions