Need a hand with 'count' on a UNION

Atari · #1 (**permalink**) 03-19-07, 01:27

Hi everyone,

I'm trying to figure how many rows are being returned by a UNION at the same time as the union itself, possibly having the total appended to the rows themselves.

Considering that the tables could look something like:

id INT(32), title VARCHAR(128), entry (TEXT) with a FULLTEXT col on title/entry

The query that fetches the proper data looks like this:

Code:

SELECT X.* FROM (

SELECT 'A' as type, id, title, MATCH (title,entry) AGAINST ( 'string' ) AS score
FROM tableA
HAVING score > 1
		
UNION ALL

SELECT 'B' as type, id, title, MATCH (title,entry) AGAINST ( 'string' ) AS score
FROM tableB
HAVING score > 1
		
UNION ALL


SELECT 'C' as type, id, title, MATCH (title,entry) AGAINST ( 'string' ) AS score
FROM tableC
HAVING score > 1
) AS X

ORDER BY X.score DESC
// possibly followed by LIMIT/OFFSET

Where some sample output may look like:

Code:

A,1,test,2.42
B,4,test,2.34
B,5,hello,1.0
C,3,world,0.5

I'd like to gather the amount of rows that query 'discovers' (for paging purposes) as well. Something akin to:

Code:

A,1,test,2.42, 4
B,4,test,2.34, 4
B,5,hello,1.0, 4
C,3,world,0.5, 4

What's the easiest route to get this done? I know how to use COUNT normally, but am unsure on how to apply it to this type of query.

Thanks as always!

r937 · #2 (**permalink**) 03-19-07, 05:11

okay, take your query and change it ever so slightly --

Code:

SELECT count(*) FROM (

SELECT 'A' as type, id, title, MATCH (title,entry) AGAINST ( 'string' ) AS score
FROM tableA
HAVING score > 1
		
UNION ALL

SELECT 'B' as type, id, title, MATCH (title,entry) AGAINST ( 'string' ) AS score
FROM tableB
HAVING score > 1
		
UNION ALL


SELECT 'C' as type, id, title, MATCH (title,entry) AGAINST ( 'string' ) AS score
FROM tableC
HAVING score > 1
) AS X

and now add it as a subquery to each SELECT statement in your original query --

Code:

SELECT X.* FROM (

SELECT 'A' as type, id, title, MATCH (title,entry) AGAINST ( 'string' ) AS score
   , ( SELECT count(*) FROM ( ... ) as X ) as C
FROM tableA
HAVING score > 1
		
UNION ALL

SELECT 'B' as type, id, title, MATCH (title,entry) AGAINST ( 'string' ) AS score
   , ( SELECT count(*) FROM ( ... ) as X ) as C
FROM tableB
HAVING score > 1
		
UNION ALL


SELECT 'C' as type, id, title, MATCH (title,entry) AGAINST ( 'string' ) AS score
   , ( SELECT count(*) FROM ( ... ) as X ) as C
FROM tableC
HAVING score > 1
) AS X

now, tell me again why this is easier than using mysql_num_rows() (or the equivalent in whatever front-end app you are using for this)

Atari · #3 (**permalink**) 03-19-07, 06:22

Thanks for the response!

I was aware of mysql_num_rows as a solution, however, I was hoping that everything could be done in a single query that wasn't as expensive as what you'd suggested above (which I'd actually come up with, but had discarded as being too verbose). The goal is to get a total for paging however, and mysql_num_rows will be limited by the LIMIT statement, where a full total is required.

If that's the only way, I suppose I've no choice! It's too bad there's no COUNT or SUM that I can perform on the outer query that can solve the issue - thanks for your expertise! I thought there might be a trick somewhere...

r937 · #4 (**permalink**) 03-19-07, 06:27

look up SQL_CALC_FOUND_ROWS in the manual

Atari · #5 (**permalink**) 03-19-07, 07:26

Thanks for the heads up on SQL_CALC_FOUND_ROWS

Internet Services

Web Development

Digital Marketing

Consumer Tech