Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    35

    Question Unanswered: Need a hand with 'count' on a UNION

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Posts
    35
    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...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    look up SQL_CALC_FOUND_ROWS in the manual

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Posts
    35
    Thanks for the heads up on SQL_CALC_FOUND_ROWS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •